ALTER SYSTEM CANCEL SQL : Cancel a SQL Statement in a Session in Oracle Database 18c

The ALTER SYSTEM CANCEL SQL command was introduced in Oracle Database 18c to cancel a SQL statement in a session, providing an alternative to killing a rogue session. If you ultimately have to kill the session, that is discussed here.

Syntax

The basic syntax of the ALTER SYSTEM CANCEL SQL statement is show below.

ALTER SYSTEM CANCEL SQL 'SID, SERIAL[, @INST_ID][, SQL_ID]';

If the INST_ID is omitted, it is assumed you mean the current instance. If the SQL_ID is omitted, it is assumed you mean the SQL that is currently running in the specified session. Some of the variations are shown below.

-- Current SQL in session on this instance.
ALTER SYSTEM CANCEL SQL '738, 64419';

-- Current SQL in session on instance with INST_ID = 1.
ALTER SYSTEM CANCEL SQL '738, 64419, @1';

-- Specified SQL in session on this instance.
ALTER SYSTEM CANCEL SQL '738, 64419, 84djy3bnatbvq';

-- Specified SQL in session on instance with INST_ID = 1.
ALTER SYSTEM CANCEL SQL '738, 64419, @1, 84djy3bnatbvq';

All four pieces of information can be retrieved from the GV$SESSION view, as shown below.

Identify the Session to be Cancelled

Cancelling a SQL statement in a background session can be very destructive, so be very careful when identifying the session and SQL.

Identify the offending session and SQL using the GV$SESSION view. The following query joins to the GV$PROCESS view to get the SPID column, which is not really necessary for this command.

SET LINESIZE 150
COLUMN spid FORMAT A10
COLUMN username FORMAT A30
COLUMN program FORMAT A45

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

The SID, SERIAL#, INST_ID and SQL_ID values of the relevant session can then be substituted into the commands in the previous sections.

oracleops-support

Wikipedia