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

Share via:

Post Views: 0

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