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

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

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)

This feature exists in 18c, 21c  (innovation release), 19c and 23ai (both long term releases).  The team at Oracle behind this feature has done a great job. I would like to credit Kishy Kumar from Oracle, who is also the lead developer and architect for reviewing this blog post.