Releasing library cache pin

Interesting question from one of my customers.

One of the sessions was killed in the process of debugging a procedure with Toad. But the library cache pin continued to be hold and couldn’t recompile or modify the procedure.

Even “shutdow immediate” got blocked, so the only one I could do was “shutdown abort”.

Why is this happening and how do I force the release of the library cache pin.

The answer is simple and clear.

When the session got termininated unsuccessfully hodling library cache pin, there is a chance that library cache pin is still hold. Killing the corresponding perfectly would release the library cache pin.

What does this mean? I’ve built up very simple test case to prove my viewpoint.

-- Session #1
-- Create procedure PROC1 which sleeps 1000 sec
UKJA@ukja102> create or replace procedure proc1
  2  is
  3  begin
  4     dbms_lock.sleep(1000);
  5
  6  end;
  7  /

Procedure created.


-- Execute procedure proc1. It would be blocked due to the TX lock contention.
-- And kill the session simply with Ctrl+C
-- The problem is that killing session with Ctrl+C(especially on Windows)
-- does not actually kill the corresponding process.
UKJA@ukja102> exec proc1;
^C
C:\Documents and Settings\exem>sqlplus ukja/ukja@ukja102

-- Session #2
-- Okay, now the session #1 got killed. 
-- But how about the procedure itself? Is it terminated or alive?
SYS@ukja10> @lock_internal 144 PROC1

       SID TY handle            mod        req NAME
---------- -- ---------- ---------- ---------- ------------------------------
       142 LK 2A3557A8            1          0 PROC1
       142 LN 2A3557A8            2          0 PROC1  <-- Here!

SYS@ukja10> @lco 2A3557A8 %

KGLHDADR                      : 2A3557A8
KGLNAOBJ                      : PROC1
TYPE                          : Parent
LOCK_HOLDER                   : 142
KGLLKMOD                      : 1
KGLLKREQ                      : 0
PIN_HOLDER                    : 142
KGLPNMOD                      : 2    <-- Here
KGLPNREQ                      : 0
-----------------

PL/SQL procedure successfully completed.

-- The session is alive holding the shared library cache pin on the PROC1.
-- No change on the procedure is allowed. Ouch!
-- It would be alive forever without proper cleanout for the corresponding resources.

-- Session #2
-- Kill the corresponding process(thread) using orakill
-- On unix, kill command
SYS@ukja10> ho orakill UKJA10 5288

Kill of thread id 5288 in instance UKJA10 successfully signalled.

-- After a couple of seconds, PMON would cleanout the resource of the killed process
-- And the library cache pin got released. 
UKJA@ukja102> @lco 2A3557A8 %
KGLHDADR                      : 2A3557A8
KGLNAOBJ                      : PROC1
TYPE                          : Parent
LOCK_HOLDER                   :
KGLLKMOD                      :
KGLLKREQ                      :
PIN_HOLDER                    :
KGLPNMOD                      :
KGLPNREQ                      :
-----------------

PL/SQL procedure successfully completed.

I hope that this simple and stupid test case clearly explains what I’m trying to say.
(lock_internal.sql is here and lco.sql is here)

I’m not sure why Toad did not release the libary cache pin in the above question. Debugging functionality given by Toad(and Oracle!) might have some critical sections that hold library cache pin on the procedure(which would be quite natural with debugging function) and fail to release it without proper termination process.

About these ads