From Oracle 18c, you can terminate a SQL query consuming excessive resources in a session like runaway sql queries. This is an alternative of killing the whole session. When you cancel a DML statement, the statement is rolled back.

Here is the syntax of cancelling a SQL statement.

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

  • If @INST_ID is not specified , the instance id of the current session is used.
  • If SQL_ID is not specified, the currently running SQL statement in the specified session is canceled.

Example:

Cheers
Mandy

Leave a Reply