Cancel a Query running in another session without killing the session

Hi,

If you want to cancel a query running but not kill the session, you can do that by following these steps below:



1-Create a new session and run something :

  SYS@ORCL>
set serveroutput on
create or replace procedure testing
as
begin
  while 2>1 loop
dbms_output.put_line('Testing killing query!!');
dbms_lock.sleep(1);
end loop;
end;
/

exec testing;


2-On another session , check sid and serial of your session:

SYS@ORCL> select a.sid,s.serial# from v$access a, v$session s where a.object='TESTING' and a.sid = s.sid;



3-Now, cancel your query but not kill the session

SYS@ORCL> exec dbms_system.set_ev(3968,641,10237,4,'');

4-On your fist session, you will see something like below:

SYS@ORCL> exec testing
Testing killing query!!
Testing killing query!!

BEGIN testing; END;

*
ERROR at line 1:
ORA-01013: o usuario solicitou o cancelamento da operac?o atual
ORA-06512: em "SYS.DBMS_LOCK", line 205
ORA-06512: em "SYS.TESTING", line 6
ORA-06512: em line 1


SYS@ORCL>


5-Make sure that you will TURN OFF the event on your session. Otherwise you will get ORA-01013 forever.

SYS@ORCL> exec dbms_system.set_ev(3968,641,10237,0,'');

5-To make sure your session wans't killed, run any new query on your old session:

SYS@ORCL> select count(*) from dict;

  COUNT(*)
----------
      2620
   
   
   
Best Regards,
Paulo Portugal