Killing an Oracle process from inside Oracle
Filed under: Internals, Oracle, Troubleshooting - Tanel Poder @ 7:44 pm
I had a following situation few days ago - I was running a CREATE TABLE AS SELECT over a heterogenous services dblink. However I cancelled this command via pressing CTRL+C twice in Windows sqlplus (this actually just kills the client sqlplus and not the call).

Anyway, when I wanted to drop that table involved, this happened:

SQL> drop table MYTABLE;
drop table MYTABLE
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

I can’t drop a table as someone is holding a lock on it. Fair enough, this was a dev environment used only by me, so I used DBA_OBJECTS.OBJECT_ID to find out the object ID of that table:

SQL> @o MYTABLE

owner                     object_name                    object_type        CREATED           LAST_DDL_TIME     status           OID      D_OID
------------------------- ------------------------------ ------------------ ----------------- ----------------- --------- ---------- ----------
XYZ_DEV01_OWNER           MYTABLE                        TABLE              20080616 11:08:44 20080616 11:08:44 VALID          63764      63764

…and then I queried what enqueue locks were held on that object:

SQL> select * from v$lock where id1=63764;

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
40034278 40034290        130 TM      63764          0          6          0       2662          0

Ok, I see session 130 holding a TM lock on that table. I queried the corresponding SERIAL# from v$session as well and killed the session:

SQL> alter system kill session '130,8764';
alter system kill session '130,8764'
*
ERROR at line 1:
ORA-00031: session marked for kill

SQL> select * from v$lock where id1=63764;

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
40034278 40034290        130 TM      63764          0          6          0       2668          0

After hanging for 60 seconds, my kill command gave up (and marked my session for kill), but my lock was still not released… Now what?

This happens when the victim session is so stuck somewhore in an Oracle call that it never has a chance to receive the message it has been killed. And apparently some resources used can in that call can’t be released (for whatever reason, it may be by design, it may just be a bug).

The below queries against V$SESSION and V$LOCK show that even though the session has been marked to be in killed status, it’s still holding a lock:

SQL> @usid 130

USERNAME                SID                 AUDSID OSUSER           MACHINE            PROGRAM              SPID          HASH_VALUE   LASTCALL STATUS
----------------------- -------------- ----------- ---------------- ------------------ -------------------- ------------ ----------- ---------- --------
XYZ_DEV01_OWNER          '130,8764'          33533 1288249          \XYZHOST001        sqlplus.exe          3872          3564023715       4032 KILLED

SQL> select * from v$lock where id1=63764;

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
40034278 40034290        130 TM      63764          0          6          0       2695          0

Ok, I tried various other options, like kill immediate and disconnect, which should have higher chance to clean up my session properly:

SQL> alter system kill session '130,8764' immediate;
alter system kill session '130,8764' immediate
*
ERROR at line 1:
ORA-00031: session marked for kill

SQL> alter system disconnect session '130,8764' immediate;
alter system disconnect session '130,8764' immediate
*
ERROR at line 1:
ORA-00031: session marked for kill

SQL> select * from v$lock where id1=63764;

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
40034278 40034290        130 TM      63764          0          6          0       2710          0

Still no luck, lock is there.

So I found the OS PID of my server process (or actually OS Thread ID inside oracle.exe process as I was on Windows) and used oradebug short_stack to check where that process was stuck (the output is slightly formatted):

SQL> oradebug setospid 3872;
Oracle pid: 18, Windows thread id: 3872, image: ORACLE.EXE (SHAD)

SQL> oradebug short_stack;
_ksdxfstk+14<-_ksdxcb+1481<-_ssthreadsrgruncallback+428<-_OracleOradebugThreadStart@4+819
<-7C80B680<-00000000<-71A557C4<-71AB4376<-6298540C<-6298325E<-60A0D931<-609D005F<-609B073D<-609AF9
endExitAgent+202<-_hoxexit+188<-_hogmdis+890<-_hogdisc+8<-_xupidhs+137<-_upidhs+20<-_kpudtch+305
<-_OCIServerDetach+12<-_ncodncf+268<-_npidr0+2300<-_npifcc+46<-_qerrmFBu+457<-_qerrmFetch+1
+1291<-_opiodr+1099<-_rpidrus+178<-_rpidru+88<-_rpiswu2+426<-_rpidrv+1461<-_psddr0+449
<-_psdnal+283<-_pevm_EXIM+153<-_pfrinstr_EXIM+34<-_pfrrun_no_tool+56<-_pfrrun+781<-_plsql_run+738
<-_pr+1099<-_opidrv+819<-_sou2o+45<-_opimai_real+112<-_opimai+92<SQL>