Administration

How to stop a query or transaction in another session without killing the session

User Rating:  / 2
PoorBest 
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: 7119

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