Administration
How to stop a query or transaction in another session without killing the session
User Rating: / 2
- Details
-
Parent Category: Articles
-
Created on Friday, 04 January 2013 10:51
-
Last Updated on Friday, 04 January 2013 10:53
-
Published on Friday, 04 January 2013 10:51
-
Written by Guy Lambregts
-
Hits: 7154
How to stop a query or transaction in another session without killing the session ( using event 10237 )
We are DBA' s and would like to stop a weird running query, a too odd
transaction in another users session. We can achieve this by killing that session.
However there are options to stop a query or transaction in another session without killing the session
This is the how to
Option 1 : DBMS_SYSTEM ( as sys )
We grap the session identifier sid and serial# from v$session.
SQL_DBA > select v.sid,v.serial#,p.spid
2 from v$session v,v$process p
3 where v.paddr = p.addr
4 and v.username = 'TUNING';
SID SERIAL# SPID
---------------- ---------------- ------------------------
29 5 6277
We stop the execution of the query / transaction
SQL_DBA > exec dbms_system.set_ev(29,5,10237,1,'');
PL/SQL procedure successfully completed.
The session receives
ERROR:
ORA-01013: user requested cancel of current operation
After this the session is not able to initiate other queries / transactions the session keeps on receiving
SQL_USER > select * from global_name;
select * from global_name
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
However we can disable event 10237
SQL_DBA > exec dbms_system.set_ev(29,5,10237,0,'');
PL/SQL procedure successfully completed.
Once done the session is able to continue without the need to reconnect
SQL_USER > select * from global_name;
GLOBAL_NAME
-----------
SILVER
Option 2 : ORADEBUG ( as sys )
SQL_DBA > oradebug setospid 6277;
Oracle pid: 30, Unix process pid: 6277, image: This email address is being protected from spambots. You need JavaScript enabled to view it.
(TNS V1-V3)
SQL> oradebug session_event 10237 trace name context forever, level 1;
Statement processed.
The session receives
ERROR:
ORA-01013: user requested cancel of current operation
After this the session is not able to initiate other queries / transactions, the session keeps on receiving
SQL_USER > select * from global_name;
select * from global_name
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
However we can disable event 10237
SQL_DBA > oradebug session_event 10237 trace name context off;
Statement processed.
Once done the session is able to continue without the need to reconnect
SQL_USER > select * from global_name;
GLOBAL_NAME
-----------
SILVER
Note that one can alse suspend and resume queries / transactions in another’ s user session using oradebug
SQL_DBA > oradebug suspend;
Statement processed.
SQL_DBA > oradebug resume;
Statement processed.
Worth to know