ITPub博客

首页 > 数据库 > Oracle > using 10237 event to STOP A QUERY OR TRANSACTION IN ANOTHER SESSION

using 10237 event to STOP A QUERY OR TRANSACTION IN ANOTHER SESSION

Oracle 作者:warmbreeze 时间:2016-10-07 01:37:51 0 删除 编辑
OPTION 1 : DBMS_SYSTEM ( AS SYS )
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: oracle@pcguyl (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.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/37279/viewspace-2125884/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-02-15

  • 博文量
    45
  • 访问量
    42544