Cancel a SQL Query in a Session in Oracle 18c ( 12.2.0.2 ) Version

Cancel a SQL Statement in a Session in Oracle Database 18c 12.2.0.2 Version

In Oracle 18c, added new feature in which you can cancel a particular SQL without breaking the Session established between applications

Syntax for cancelling a SQL statement:

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

Note:
SID – Session ID
SERIAL – Session Serial Number
INST_ID – Instance ID
SQL_ID – SQL ID of SQL statement

Example:

-- In case of single instance
ALTER SYSTEM CANCEL SQL '34, 54321, 5nxr4zv6z8pjm';

-- In case of RAC instance
ALTER SYSTEM CANCEL SQL '34, 54321, @1, 5nxr4zv6z8pjm';

Find the SQL Session details , SQL id and SQL Text for verification:

set pagesize 100 linesize 132
col username format a10
col sql_text format a60
SELECT USERname,terminal,SID,SERIAL#,a.module,b.sql_id,SQL_TEXT
FROM V$SESSION a, V$SQL b
WHERE a.SQL_ADDRESS = b.ADDRESS
AND a.STATUS = 'ACTIVE'
AND SQL_TEXT not like '%USERname%'
AND username IS NOT NULL;

Find the SQL id , SQL Session details and instance id

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

Find the SQL id , Service name, module and status

col service_name for a15 wrapped
col username for a15 wrapped
col module for a15 wrapped
col action for a15 wrapped
select s.username,s.service_name,s.module,s.action,s.status,s.sid,s.serial#
from v$session s,v$process p
where s.paddr=p.addr and service_name != 'SYS$BACKGROUND';

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.