The idea of this post is to explain the steps to cancel a query in Oracle Database 18c. In this version, you have the option to cancel just the query instead of terminating the session.
The following clauses are required in an “ALTER SYSTEM CANCEL” SQL statement:
- SID – Session ID
- SERIAL – Session serial number
Here is an example:
1. Find SID and SERIAL#
Let us first find the SID and SERIAL# for the query to be cancelled.
##### Session 1 ##### SQL> select sid, serial# from v$session where sid = userenv('SID'); SID SERIAL# ---------- ---------- 262 28686 SQL> select count(*) from test,test;
2. Cancel the query
Use the SQL statement “ALTER SYSTEM CANCEL” to cancel the query using the SID and SERIAL# found in step 1 above.
##### Session 2 ##### SQL> alter system cancel sql '262,28686'; System altered.
3. Verify
For the Session 1, you can verify if the SQL query has been killed. You should see the output below for the query fired in the step 1:
SQL> select count(*) from test,test; select count(*) from test,test * ERROR at line 1: ORA-01013: user requested cancel of current operation.
Note: Using this method you do not need to kill the whole session.