Tag: terminate

How to stop a job scheduled in DBMS_SCHEDULER?

Using sys.dbms_scheduler.STOP_JOB one can stop scheduled job. SQL> exec sys.dbms_scheduler.STOP_JOB(job_name=>'SYS.ORA$AT_OS_OPT_SY_12856', force=>true); Output: PL/SQL procedure successfully completed. If it can't find the job then you may see the following error, in the example below I hadn't specified the user to it was looking for the job as the current user. ORA-27475: "DBAUSER.ORA$AT_OS_OPT_SY_12856" must be a job … Continue reading How to stop a job scheduled in DBMS_SCHEDULER? →

Script to kill/terminate sessions connected to a specific database

DECLARE @dbName VARCHAR(100) SET @dbName = 'AdventureWorks' DECLARE @sql VARCHAR(50) DECLARE @spid SMALLINT DECLARE @loginame NVARCHAR(256) DECLARE @hostname NVARCHAR(256) DECLARE @open_connections INT DECLARE db_cursor CURSOR FOR SELECT spid, loginame, hostname FROM master.sys.sysprocesses WHERE dbid = DB_ID(@dbName) SELECT @open_connections = count(1) FROM master.sys.sysprocesses WHERE dbid = DB_ID(@dbName) PRINT 'Killing ' + RTRIM(@open_connections) + ' connections to … Continue reading Script to kill/terminate sessions connected to a specific database →

How to terminate and rollback a SQL script when running in SQL*Plus when an error is encountered?

Adding the following line before running a SQL Script will terminate the SQL script from running and rollback the change when an error is encountered. WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK; Example: SQL> select * FROM test; no rows selected SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK; SQL> insert into test values ( 1 ); 1 row … Continue reading How to terminate and rollback a SQL script when running in SQL*Plus when an error is encountered? →

Script to generate SQLs to terminate rman sessions

Killing the rman main process from the command line doesn't immediately kill the rman sessions using the script below which will create SQL statements to kill your RMAN sessions in Oracle and in most cases it will also terminate the oracle shadow process. The script below generates ALTER statements to kill oracle session(s) and a … Continue reading Script to generate SQLs to terminate rman sessions →