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

10 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
    Replies
    1. It is as effective as Control+C (^C)

      Delete
  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