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
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
This is really Great.
ReplyDeleteThanks for Sharing this info.
Raj Kaushik
Alwayz welcome !!!
ReplyDeleteThanks 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!
ReplyDeleteKeep looking for new tip :)
ReplyDeletehavent tried .. but sounds very interesting and useful
ReplyDeleteIt is as effective as Control+C (^C)
DeleteIf dml operation stop after 30 minutes, it means smon will rollback dml data.
ReplyDeleteWill it not create performance issue in dataware house
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.
Deletehi nimish,
ReplyDeleteif 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"
consider connecting pooling scenario, where killing session may invalidate the pool.
DeleteQuite Useful
ReplyDeletelooking for this 2 days back..Good info
ReplyDeleteThanks for sharing!
ReplyDeleteThank you, very useful!
ReplyDeleteI 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
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