Oracle: Stopping Query without killing the session

There is an Oracle event, 10237, which is described as "simulate ^C (for testing purposes)".

You should have the SID and SERIAL# of the session you want to interrupt.

Call SYS.DBMS_SYSTEM.SET_EV( sid, serial#, 10237, 1, '' ) to activate the event in the target session. Any currently executing statement should be interrupted (receiving "ORA-01013: user requested cancel of current operation").
As long as the event is set, any further statements the session attempts to execute will immediately terminate with the same error.

To deactivate the event, make the same call with the fourth parameter set to "0". The session will then be able to execute statements again.

Here's some sample code. This is meant to be run as an anonymous block in SQLPlus, with substitution variables "sid" and "serial" defined appropriately. You could turn it into a stored procedure with those as its parameters.


exec sys.dbms_system.set_ev(&sid, &serial, 10237, 1, '');
exec sys.dbms_system.set_ev(&sid, &serial, 10237, 0, '');



NOTE: Your user must have permission to execute it and it will be granted  by SYSDBA as

grant execute on  dbms_system to my_user;




Related Post:
- ORA-00027 cannot kill current session
- ORA-00054: resource busy and acquire with NOWAIT specified

15 comments:

  1. This is really Great.
    Thanks for Sharing this info.

    Raj Kaushik

    ReplyDelete
  2. Thanks for sharing this useful information. I often get stuck into situation where for stopping a query I need to kill that session. Now I get this code which surely helps me to stop query without killing that session. Great work!

    ReplyDelete
  3. havent tried .. but sounds very interesting and useful

    ReplyDelete
  4. If dml operation stop after 30 minutes, it means smon will rollback dml data.

    Will it not create performance issue in dataware house

    ReplyDelete
    Replies
    1. why would any one stop a necessary DML operation. DML operation must be stopped only if it is blocked or blocking some other high priority session or DML is totally not required.

      Delete
  5. hi nimish,
    if it would stop the running query and wont allow any new queries,why would we want the sesion to be active ,instead we can kill the session directly ,what is the advantage in using this as alternative to "killing a session"

    ReplyDelete
    Replies
    1. consider connecting pooling scenario, where killing session may invalidate the pool.

      Delete
  6. looking for this 2 days back..Good info

    ReplyDelete
  7. Thanks for sharing!

    ReplyDelete
  8. Thank you, very useful!
    I could reproduce the behaviour described in 11.2.0.4 and 12.1.0.2.
    However in Oracle 19c this event seems to work differently. A running select gets Oracle error "ORA-01031: insufficient privileges" when the event is set from another session. New selects are possible, though. So, the scope of the event seems to be limited to the active SQL of a session. Probably the underlying mechanism was changed also - if we can trust the error message.
    Disclaimer: only a quick, preliminary test was made.
    KR Friedrich

    ReplyDelete
  9. I have seen the same behavior: Number 1031 instead of 1013. It seems to be like this since Oracle 12.2.0.1. Probably someone in development has reversed two digits.

    ReplyDelete