Data … as usual

All things about data by Laurent Leturgez

Event for crashing an oracle session

Thanks to Grimm for this information relayed by Laurent Schneider.

This is a tricky method for crashing a session without disconnecting the user :

Session 1 :

SQL> begin
2 while 2>1 loop
3 null;
4 end loop;
5 end;
6 /

Session 2 (logged as user SYS or as user who has privilege to execute the packageDBMS_SYSTEM.SET_EV) :

SQL> exec dbms_system.set_ev(sid,serial#,10237,4,'');
PL/SQL procedure successfully completed.

Session 1 :

ERROR at line 1:
ORA-01013: user requested cancel of current operation

The session is not killed, user is still connected –

If you want to perform other queries, administrator have to invalidate this event (exec dbms_system.set_ev(sid,serial#,10237,0,”);) otherwise you will encountered some “ORA-00604: error occurred at recursive SQL level 1”

SQL> select * from v$version;
Oracle Database 11g Enterprise Edition Release - 64bit

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: