by 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CDB$ROOT@CDB18> select inst_id, sid,serial#,username, sql_id from gv$session where username like 'TEST'; INST_ID SID SERIAL# USERNAME SQL_ID ---------- ---------- ---------- ---------------------------------------- ------------- 1 107 26389 TEST 5vtwdnsqbxu3m CDB$ROOT@CDB18> alter system cancel SQL '107,26389'; System altered. CDB$ROOT@CDB18> alter system cancel SQL '107,26389, @1'; System altered. CDB$ROOT@CDB18> alter system cancel SQL '107,26389, 5vtwdnsqbxu3m'; System altered. CDB$ROOT@CDB18> alter system cancel SQL '107,26389, @1, 5vtwdnsqbxu3m'; System altered. |
Cheers
Mandy
Leave a Reply