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.
Email ID: firstname.lastname@example.org