alter-system-cancel-sql-18c

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 [46]here.
* [47]Syntax
* [48]Identify the SQL to be Cancelled

Related articles.
* [49]Killing Oracle Sessions (ALTER SYSTEM KILL / DISCONNECT
SESSION)
* [50]Clearing Down Old Database Sessions

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.

For more information see:
--------------------------
Source .... : https://oracle-base.com/articles/18c/alter-system-cancel-sql-18c
--------------------------