Cancel Session in Oracle 18c

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“.

Conclusion

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.

Source

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