Oracle 18c new feature: ALTER SYSTEM CANCEL SQL

In the past when you wanted to terminate an Oracle database session that is causing a problem we used the classical SQL command:

SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#’;

In 18c a new command is introduced , that will cancel the SQL statement being executed in the session without actually terminating the session:

SQL> ALTER SYSTEM CANCEL SQL ‘SID, SERIAL,SQLID’;

To simulate:

To find the 3 values SID, SERIAL, and SQLID you can use the following query:

SELECT s.inst_id,

s.sid,

s.serial#,

s.sql_id,

p.spid,

s.username,

s.program

FROM   gv$session s

JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE  s.type != ‘BACKGROUND’;

PIC1

SQL> ALTER SYSTEM CANCEL SQL ‘874,51416,a5ks9fhw2v9s1’;

PIC2

The user session will generate the following error:

ORA-01013: user requested cancel of current operation

However, the user will still continue working fine (his session will not be terminated as shown below)

PIC3

 

 

 

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s