Cancel SQL statement Oracle Database 18C

In latest version of database 18C instead of killing user session you can cancel its currently running or opened SQL statement using the ALTER SYSTEM CANCEL SQL statement.

Full syntax

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

Required parameters

  • SID – Session ID
  • SERIAL – Session serial number

Optional parameters

  • INST_ID – Instance ID
  • SQL_ID – SQL ID of the SQL statement

Example

Canceling a SQL statement having the session identifier of 15, session serial number of 20, instance 4

ALTER SYSTEM CANCEL SQL '15,20,@4';

Canceling a SQL statement having the session identifier of 20, session serial number of 51142, and SQL ID of 8vu7s907prbgr:

ALTER SYSTEM CANCEL SQL '20, 51142, 8vu7s907prbgr';

It means you can just cancel one of many opened cursors in a given session – it’s great option.

NOTE: When you cancel a DML statement, the statement is rolled back

Have a fun 🙂

Tomasz

 

Leave a Reply

Your email address will not be published. Required fields are marked *

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