Skip to content

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 :




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’);


———- ———-

275 41479

SQL> create table t_source as select * from dba_source where 1=2;

Table created.

SQL> select count(*) from t_source;




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;



296264  <============================

Here, we can see, only the second Insert statement (which was cancelled) got rolled back



%d bloggers like this: