How to CANCEL a query running in another session?

Here’s a treat for Oracle geeks, hackers and maniacs out there…

Update 1: 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:

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 ;-)

Update 2: Oracle 18c brings another option, you can issue an ALTER SYSTEM CANCEL SQL ‘SID, SERIAL, @INST_ID, SQL_ID’ syntax to cancel a running SQL statement. [Link]

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;



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:

NB! I am running one more Advanced Oracle Troubleshooting training in 2018! You can attend the live online training and can download personal video recordings too. The Part 1 starts on 29th January 2018 - sign up here!

This entry was posted in Oracle. Bookmark the permalink.

24 Responses to How to CANCEL a query running in another session?

  1. Sean says:

    Very interesting! I learn a lot from you!

    Is it possible to corrupt the database by cancelling a DML transaction this way? Or it behaves exactly same as if we press ‘CTRL+C’ and rely on the rollback mechanism?


  2. Tanel Poder says:


    Sean, I’d say it’s not worse than just doing kill -9 on a process which is completely ungraceful exit.

    The URG signal on the other hand is an expected and handled signal in Oracle (and it can arrive any time as Oracle doesn’t have control over when a user sends the cancel from the app).

    Of course you can always hit a bug as this “technique” is not field-tested by lots of users like other features are. It’slike running ORADEBUG commands which arent widely used.

    So, as I said, this is not for everyday production problem solving, its just a treat to fellow Oracle geeks around who are interested in how things work.

  3. null says:

    We can use oracle db feature. 11.1 and higher…

    session_id IN NUMBER,
    session_serial IN NUMBER,
    consumer_group IN VARCHAR2);
    If the consumer_group name is ‘CANCEL_SQL’, the current call will be canceled.


  4. Tanel Poder says:

    Nice! Didn’t know about that.
    I checked docs briefly – this feature seems to be there since Oracle 10.1 (or maybe even earlier, didn’t check 9i docs).

  5. null says:

    Oracle db developers take care about us! ;)

  6. null says:

    Tanel, thanx for your activities (this blog and new website) – its very useful ;)

  7. Sumit says:

    Hi Tanel,
    Is there any way, we can figure the between ‘SELECT’ query running from anonymous plsql block and stand alone.
    The command_type shows 3 for select in v$sql for anonymous plsql till the select query is running and changes to 47 when pl/sql execute occurs.

  8. Sumit says:

    This will also cancel the DML..

  9. Tanel Poder says:

    The resource manager approach would cancel any database call – however how its implemented is that the target process (which we want to cancel) needs to check some flags to see if anyone wants to cancel it. It does so every now and then. However, if the target process is hopelessly stuck in some loop (due a bug) then it may never check that flag and never cancel itself. That’s where the kill -URG (or indeed kill -9) will help :)

  10. Interesting! I look forward to meeting you at Hotsos next week.


  11. Alex says:

    Hi Tanel,
    set event 10237

  12. Noons says:

    Thanks for this one, Tanel. Had a chance to use it last Friday and it works a treat.

  13. Does “kill -URG” work in Linux? I have long running query and tried:

    kill -URG 29086
    kill -s SIGURG 29086
    kill -s SIGINT 29086
    kill -2 29086

    – all of them were ignored by the process. I can cancel the query any moment by sending OCIBreak from the client program which started the query. Because many applications are not able to send OCIBreak, it will be nice to be able to cancel by “kill”, but does it work in Linux or am I missing something?

  14. Tanel Poder says:

    @Sergei Romanenko
    Sergei, it should work on Linux server as well. What’s your client OS?

    If on Windows client OS, then at least when running from sqlplus, the kill -URG trick won’t work as I explained in my post. Maybe in case of some other client app/OCI library versions the same applies. You can run strace on your server process and see whether it does a lot of poll() syscalls when you run something in the server, if yes, then the break by urgent signal handling has not been set up/requested by client.

  15. Tanel, sorry for my delay in reply. Yes, you are right. When connecting from Linux client, “kill -URG” works. When connecting from Windows, it doesn’t. And yes, the server process does a lot of poll() system calls.

    I reread the article, still not sure I understand the difference. Does it mean, when server process is started on behalf of win client, it doesn’t register Urgent signal handler? Instead it polls the socket in the execution loop (quite often) to check if OCIBreak was sent by the client? If so, then in theory win client could fool the server process and pretend it was a linux/unix client and the “kill – URG” would work?

  16. Tanel Poder says:

    @Sergei Romanenko
    Sergei, yep when connecting from *some* windows clients then the client tells Oracle during connect that it can not send urgent packets, thus Oracle switches to the out-of-band break polling (you can disable that with DISABLE_OOB parameter in sqlnet.ora, then you can’t cancel a query at all).

    Yep in theory if you could make the windows client to somehow say Oracle server it supports urgent packets then oracle would set up the signal handler properly and you could use the URG signal to cancel the query… In practice I wouldn’t waste time trying, especially in production…

  17. Tanel, thanks a lot! Now it’s clear how it works.

  18. I had no luck on on Linux:
    1 begin
    2 dbms_resource_manager.switch_consumer_group_for_sess(
    3 27,25,’CANCEL_SQL’);
    4* end;
    SQL> /
    ERROR at line 1:
    ORA-29366: invalid CONSUMER_GROUP argument specified
    ORA-06512: at “SYS.DBMS_RMIN”, line 505
    ORA-06512: at “SYS.DBMS_RESOURCE_MANAGER”, line 916
    ORA-06512: at line 2

    • David says:

      HI Mladen

      It’s a bug in There’s a patch (10625145) which fixes the error you received. I’ve just applied it as I have to come up with a way to cancel queries
      for user’s that have more than 3 open active sessions whilst keeping the session alive!


  19. Ram Gautam says:

    i read the this blog. i am facing similar kind of problem. My background is java programmer.. i am running dynamic query triggering from my java application. and that sql query is being runned by following way:
    sqlplus -L user./password @ .sql
    my main problem is how to stop that running query through my application.? or any session_id or oracle job_ id can give by sqlplus of that specific query?

    Thanks and Regards,

  20. Yuri says:

    I managed to cancel query using dbms_resource_manager on 10g (
    I created resource plan which had group with directive containing “SWITCH_TIME=>300, SWITCH_GROUP=>’CANCEL_SQL'”
    Then I switched corresponding group for session of interest and after roughly 300 seconds query was canceled.
    Query was doing huge hash join when it was canceled

Leave a Reply

Your email address will not be published. Required fields are marked *