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)