As of Oracle 18c , you can not only abort a database session, but also just a single SQL command. This tip shows how it works.
There are situations where the execution of SQL commands takes too long and there is a desire to cancel them. Be it because the SQL command is incorrect, or the current data situation leads to this problem. If, in a database session, such a SQL command is to be executed and aborted externally, the database session itself must be aborted by Oracle 12c by identifying it via the SID and Serial #:
SELECT sid, serial#, status, server FROM v$session WHERE username = 'SCOTT'; SID SERIAL# STATUS SERVER ---------- ---------- -------- --------- 52 40785 ACTIVE DEDICATED 285 56465 ACTIVE DEDICATED ALTER SYSTEM KILL SESSION '285,56465';
With the abort of the database session but lost more than just the running SQL command. The context of a database session, such as work steps already executed or set application parameters, is then no longer available. For this reason, there is often a desire to abort only the running SQL and continue to persist the database session. This is easily possible starting with Oracle 18c by using the following instead of the command shown above:
SELECT sid, serial#, status, server FROM v$session WHERE username = 'SCOTT'; SID SERIAL# STATUS SERVER ---------- ---------- -------- --------- 52 40785 ACTIVE DEDICATED 285 56465 ACTIVE DEDICATED ALTER SYSTEM CANCEL SQL '285,56465';
For example, in the affected database session, this looks like this:
select count (*) from all_objects, all_objects; select count (*) from all_objects, all_objects * ERROR at line 1: ORA-01013: user requested cancel of current operation
If the aborted SQL command was executed as part of a script, the script is processed further. In the following example, a script contains two SQL commands and the first command is aborted by the administrator:
Script test.sql: select count (*) from all_objects, all_objects; select count (*) from user_sequences; start test select count (*) from all_objects, all_objects * ERROR at line 1: ORA-01013: user requested cancel of current operation COUNT (*) ---------- 1
The command ALTER SYSTEM CANCEL SQL can be used with up to four parameters:
ALTER SYSTEM CANCEL SQL 'sid, serial#, instance_id, sql_id';
The first two parameters SID and SERIAL# must be specified. In a RAC environment, use the third parameter to specify the instance to which the SID and SERIAL# values refer. If you do not specify anything here, the instance in which you call the command is meant. With the fourth parameter you can still specify the SQL_ID of the SQL command which should be aborted. If this parameter is not specified, the current command is aborted. The fourth parameter is useful if you want to make sure that the currently running command, which should be aborted, is not terminated by chance and that the next command is affected by its “CANCEL SQL“.
If you want to break an out-of-control SQL command, you can now execute it in such a way that the affected database session continues to run.
Stay tuned for More articles on Oracle 18c
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
Anuradha’s LinkedIn: Anuradha’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
I tested and can assure: this is fully working on 12.2!
I also tested on 11g and 12.1. Not working. It arrived (silently and not supported, but working) on 12.2.
Even 18c, Also part of 12.2 version.
Thanks for your testing.