Description:-
Before 18c, If we want to terminate a SQL query for consuming excessive resources in a session or to cancel the blocking session or cancel incorrect SQL statement, the only way is to find out the particular session’s SID, SERIAL id and the kill the session using following command ALTER SYSTEM KILL SESSION ‘sid,serial#’;.
After 18c, instead of kill the originating session we can cancel the particular SQL of another session using “ALTER SYSTEM CANCEL SQL ‘SID, SERIAL'”.
If we are cancel a DML statement, the statement has been rolled backed.
SQL> select banner_full from v$version;
BANNER_FULL
———————————————————–
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
Version 18.2.0.0.0
Step:1 To find the SID for current session.
SQL> select distinct sid from v$mystat;
SID
———-
280
SQL> begin loop null; end loop; end;
/
Step:2 This command help us to find out the SQL_ID using the SQL_TEXT from particular SQL statement.
SQL> select sql_text,sql_id from v$sql where sql_text like ‘%loop null; end loop%’;
SQL_TEXT SQL_ID
——- ————————– ————-
select sql_text,sql_id from v$sql 6vbb9d7zj9t5w where sql_text like ‘%loop null; end loop%’
begin loop null; end loop; end; 3tfmdd4xagv3y
Step:3 To find the SERIAL# using the session ID.
SQL> select serial# from v$session where sid=280;
SERIAL#
———-
22827
Step:4 Here is the syntax of cancelling a SQL statement.
ALTER SYSTEM CANCEL SQL ‘SID, SERIAL’
SQL> alter system cancel sql ‘280,22827,3tfmdd4xagv3y’;
System altered.
SQL> begin
loop null;
end loop;
end;
/
begin
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
Catch Me On:- Hariprasath Rajaram
LinkedIn: https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook: https://www.facebook.com/HariPrasathdba
FB Group: https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba
