interrupt/stop any oracle session

Many times, we have request from development or testing team to cancel their ongoing queries. To deal with such request, we (DBA) have to find session and kill them.

Now where is the way to stop the ongoing sql query for any session. All you should have the SID and SERIAL# of the session you want to interrupt.  

SQL> conn jay/jay

Connected.

SQL>

SQL> create table test23 ( x int, constraint t_pk primary key(x) );

Table created.

SQL> insert into test23 values (1);

1 row created.

SQL> insert into test23 values (2);

1 row created.

SQL> insert into test23 values (9999999999);

1 row created.

SQL> commit;

Commit complete.

SQL> begin

  2  for i in 2 .. 999999

  3  loop

  4  delete from test23 where x = i;

  5  commit;

  6  insert into test23 values (i+1);

  7  commit;

  8  end loop;

  9  end;

 10  /

begin

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

ORA-06512: at line 6

From other terminal, I checked the sid & serial id for required session and executed the below session.

SQL> select sid,serial# from v$session where schemaname='JAY';

       SID    SERIAL#

---------- ----------

       137         11

SQL> exec sys.dbms_system.set_ev(137,11, 10237, 1, '');

PL/SQL procedure successfully completed.

Once executed, it cancel in the ongoing query. We can see ongoing pl-sql procedure is cancel with ORA-01013 error. As long as the event is set, any further statements the session attempts to execute will immediately terminate with the same error.

SQL> insert into test23 values (3);

insert into test23 values (3)

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

To deactivate the event, make the same call with the fourth parameter set to "0". The session will then be able to execute statements again.

SQL> exec sys.dbms_system.set_ev(137,11, 10237, 0, '');

PL/SQL procedure successfully completed.

SQL>

You can see I can start further operation without re-connecting.

SQL> insert into test23 values (3);

1 row created.

SQL> rollback;

Rollback complete.

SQL>

You should have sysdba priviledge to execute dbms_system or can be granted to any other oracle user. 

grant execute on  dbms_system to my_user;

Thanks,

Jay vardhan