How to CANCEL a query running in another session?
Here’s a treat for Oracle geeks, hackers and maniacs out there…
Update: As the beginning says, this article was meant as something interesting about Oracle’s internals and CTRL+C / OCICancel() handling. There’s a more practical 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 (
session_id IN NUMBER,
session_serial IN NUMBER,
consumer_group IN VARCHAR2);
Thanks to commenter “null” for this info. Note that I haven’t tested how/whether this feature works correctly so there’s homework for you ;-)
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:
- The user presses CTRL+C or clicks some button in App which runs OCICancel()
- 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
- 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)
- Unix interrupts whatever the current process was doing and calls the URGENT signal handler function (which Oracle has registered during process startup)
- 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:
Recent Comments