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.