Termination of Run-away SQL
Manage runaway queries in the database , for versions 18c and above.
From 11g onwards, you can use the Resource Manager plan directives :
SWITCH_GROUP => ‘CANCEL_SQL’
OR
SWITCH_GROUP => ‘KILL_SESSION’
to cancel the SQL or kill the session respectively. You can also change the consumer group for the runaway queries. For details, refer to the document:
Managing and Monitoring Runaway Query Using Resource Manager Note 1600965.1
Along with these, you could kill the session using the ALTER SYSTEM KILL SESSION command.
From 18c onwards, you can manually cancel any runaway query using “ALTER SYSTEM CANCEL SQL” statement, instead of killing the session. Please note this is in addition to the options available through the Resource Manager.
The following clauses are required in an ALTER SYSTEM CANCEL SQL statement:
SID – Session identifier
SERIAL – Session serial number
SQL_ID – SQL ID of the SQL statement (optional)
INST_ID – Instance ID (optional)
If the SQL_ID is not provided, the currently running statement will be cancelled.
Cancelling a DML will result in implicit rollback of that statement.
For Example:
Session 1
SQL> select sid, serial# from v$session where audsid = userenv(‘SESSIONID’);
SID SERIAL#
———- ———-
275 41479
SQL> create table t_source as select * from dba_source where 1=2;
Table created.
SQL> select count(*) from t_source;
COUNT(*)
———-
0
SQL> insert into t_source select * from dba_source;
296264 rows created. <========================== Note the count here.
SQL> insert into t_source select * from dba_source;
While this is executing, Cancel the SQL from another session
Session 2
<< Killed the statement from Session1>>
SQL> alter system cancel sql ‘275,41479’;
System altered.
Check the INSERT statement in the first session
Session 1
SQL> insert into t_source select * from dba_source;
insert into t_source select * from dba_source
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SQL> select count(*) from t_source;
COUNT(*)
———-
296264 <============================
Here, we can see, only the second Insert statement (which was cancelled) got rolled back
WikiDBA 