Oracle 18C new feature: Cancel SQL instead of killing session

Share via:

Dear Readers,

In this article we will focus one of the most remarkable feature that has been introduced in Oracle 18C that is “now you can cancel SQL statement without killing session”.

Prior 18C, DBA’s had to kill a DB sessions to get rid of high load queries using “ALTER SYSTEM KILL SESSION” command.

From 18C, Instead of killing a session, now you can cancel a problematic d SQL statement in a session. When you cancel a DML statement, the statement is rolled back. This can be achieved by “ALTER SYSTEM CANCEL SQL” command.

Mandatory clauses in an ALTER SYSTEM CANCEL SQL statement are given below:

  • SID – Session ID
  • SERIAL – Session serial number

Optional clauses in an ALTER SYSTEM CANCEL SQL statement are given below:

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

The basic syntax of the ALTER SYSTEM CANCEL SQL statement is show below.

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

We need to consider below conditions for using this command:

  • If @INST_ID is not specified, by default, currently running SQL statement in the specified session on current instance will be cancelled.
  • If SQL_ID is not specified, the currently running SQL statement in the specified session is cancelled.

Below example gives more insights about how to use this feature:

Below screenshot shows DBA screen, wherein he found problematic session & cancelled it

Below screenshot shows Developer screen, wherein his SQL was cancelled by DBA but he continued to use his session for running other queries:


Hope u will find this post very useful. 🙂

Stay tuned for more articles on Oracle 18c.

Cheers!!!

Regards,

Adityanath

Email ID: adityanath.dewoolkar@gmail.com

LinkedIn: www.linkedin.com/in/adityanath-dewoolkar-07253123/

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment