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
--------------------------
Thursday, March 22, 2018
About nsaibi
Soratemplates is a blogger resources site is a provider of high quality blogger template with premium looking layout and robust design. The main mission of templatesyard is to provide the best quality blogger templates.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment