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.
No comments:
Post a Comment