Friday, May 30, 2014

ORA-00031, ORA-03113 and ORA-03114

This was tested on Oracle 11.2. I had a session in the middle of a long-running operation: 

SQL> conn user1/user1
Connected.
SQL> exec dbms_lock.sleep(3600);
 
I tried to kill it like this but Oracle gave me an ORA-00031 instead:
 
SQL> select sid, serial# from v$session
  2  where username = 'USER1'
  3  /
 
       SID    SERIAL#
---------- ----------
         7         62
 
SQL> alter system kill session '7,62'
  2  /
alter system kill session '7,62'
*
ERROR at line 1:
ORA-00031: session marked for kill
 
SQL>
 
… and the session carried on regardless:
 
SQL> conn user1/user1
Connected.
SQL> exec dbms_lock.sleep(3600);
 
I really wanted to kill it straight away so I did it like this:
 
SQL> alter system disconnect session '7,62'
  2  immediate
  3  /
 
System altered.
 
SQL>
 
… and the session died there and then:
 
SQL> exec dbms_lock.sleep(3600);
BEGIN dbms_lock.sleep(3600); END;
 
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 15294
Session ID: 7 Serial number: 62
 
SQL>
 
When I tried to carry on using it, Oracle told me I had been disconnected:
 
SQL> select sysdate from dual
  2  /
ERROR:
ORA-03114: not connected to ORACLE
 
SQL>

No comments: