Cancelling a SQL Statement in a Session

Cancelling a SQL Statement in a Session
---------------------------------------------

An interesting new feature introduced in Oracle Database 12c Release 2 - i.e.  you can cancel a SQL statement running in a session using the ALTER SYSTEM CANCEL SQL statement.



The following clauses are required in an ALTER SYSTEM CANCEL SQL statement:

• SID – Session ID
• SERIAL – Session serial number



The following clauses are optional in an ALTER SYSTEM CANCEL SQL statement:

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



The following is the syntax for cancelling a SQL statement:

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

The following example cancels 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';