How To Cancel A SQL Query In Oracle Database 18c

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:

1. SID – Session ID
2. 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.