How to CANCEL a query running in another session?

Here’s an old article of mine, explaining some of the Oracle internals and the OS touch point. It actually goes beyond explaining just commands for canceling SQL. I have written a couple of updates into the begginning of this article about SQL cancellation commands, but if you want to learn the internals, scroll down to The original article section.

Update 1: As the beginning says, this article was meant as something interesting about Oracle internals and CTRL+C / OCICancel() handling. There’s a documented way for canceling session calls if you are running Oracle Enterprise Edition and are currently using resource manager:

You can set the consumer group for a session to CANCEL_SQL to cancel its current call:

DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS
  sessionid IN NUMBER,
  sessionserial IN NUMBER,
  consumergroup IN VARCHAR2
)

Thanks to commenter “null” for this info. Note that in my brief tests (a long time ago) this feature didnt’ work correctly and I moved on, but if it works for you, please send me an example.

Update 2: Oracle 12.2 and 18c bring another option, you can issue following commands to cancel SQL statements in other sessions:

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

The topmost command cancels whatever is the currently running SQL statement, the other two allow you to select a specific SQL_ID in the other session and cancel that (as that session may have multiple cursors open and active).

Here’s an example from Oracle 12.2.0.1:

SQL> SELECT version FROM v$instance;

VERSION
-----------------
12.2.0.1.0

SQL> ALTER SYSTEM CANCEL SQL '18,36334';

System altered.

The other session received the following error:

SQL> @lotslios 10000000000
generate lots of LIOs by repeatedly full scanning through a small table...
    sys.obj$ a,
        *
ERROR at line 8:
ORA-01013: user requested cancel of current operation

Here’s the documentation for ALTER SYSTEM CANCEL SQL. Note that while this command is documented starting from Oracle 18c, it seems to work in Oracle 12.2 too. The usual caveats apply, you’ll need to be careful with undocumented commands, but the fact that it’s documented in the next Oracle version, is promising.

The original article

I recently received a question about how to cancel queries running in another Oracle session, so that the session would not be killed, but would remain alive.

Well, there’s no supported way I can tell you, but thanks to how Oracle handles out-of-band breaks on Unix platforms, you can cancel database calls using an OS tool – kill.

Before we go on, here’s how query cancellation (pressing CTRL+C in sqlplus for example) works in Oracle:

1. The user presses CTRL+C or clicks some button in App which runs OCICancel()
2. The client process sends an urgent TCP packet (which is a regular TCP packet with URG bit set) to the socket in the database server it is connected to
3. The server OS TCP stack receives the urgent TCP packet and sends URGENT signal to the process which owns the socket (the Oracle server process)
4. Unix interrupts whatever the current process was doing and calls the URGENT signal handler function (which Oracle has registered during process startup)
5. The urgent signal handler blindly assumes that the urgent signal has been received because user wants to cancel the query, stops the execution and returns back with an error: ORA-01013: user requested cancel of current operation

So, if we can’t make our application send the break packet, OCICancel() then we can just send the SIGURG signal to the Oracle process just like the OS TCP stack would do when it receives the packet with urgent bit set.

Here’s an example:

In one session I’m running a DBMS_STATS call:

SQL> exec dbms_stats.gather_database_stats;

I identify the SPID of that session’s process and send an URG signal to that process:

kill -URG 4476

And the call gets cancelled in the other session:

SQL> exec dbms_stats.gather_database_stats;
<pre>BEGIN dbms_stats.gather_database_stats; END;

*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_STATS", line 13336
ORA-06512: at "SYS.DBMS_STATS", line 13682
ORA-06512: at "SYS.DBMS_STATS", line 13826
ORA-06512: at "SYS.DBMS_STATS", line 13790
ORA-06512: at line 1

My session was not killed – I still can run queries in it:

SQL> select * from dual;

D
-
X

SQL>

This works only on Unix platforms. Also this does not work when your client application is Windows sqlplus! This is because Windows sqlplus does not set up the out-of-band break checking properly when connecting. Maybe this is because old Windows versions TCP stacks didn’t know anything about urgent TCP packets! :)

A word of warning – this stuff is not for your everyday production usage! While it works and we know how and why it works, it’s not a good idea to send “random” signals to Oracle processes at your will. So the better way is to make your application able to cancel its database calls when you want it, but well in real world its not always (or should I even say rarely) possible.

Another thing to consider is when you run Oracle with Direct NFS, there will be network connections to the NFS server used by your server process, in addition to the client-server communication. I haven’t tested what happens when you send URG packet to a process in the DNFS case.

So try this out at your own risk ;-)

If you want to know more about query cancelling and what the in-band and out-of-band break checking is then you can read one of my old blog posts about it:

• Oracle Hidden Costs Revealed Part 1