Library cache pins
                                                 Last update (2008-04-23 13:35:04)
                                                                                                                   Date added (2008-02-28 12:56:51)

Oracle uses library cache pins to manage library cache concurrency. This tip outlines a method to deal with "library cache pin" wait events that are blocking other users.

For example when someone tries to "grant execute privileges" on a procedure that is currently running may result in library cache pins.

The first step is to see who is waiting for Library Cache Pins, use the scrip for Current waiting events

column P1RAW is the "Handle Address" of the object that is blocking. Execute the following query to get the object's owner and name:
SELECT kglnaown AS owner, kglnaobj AS OBJECT 
FROM sys.x$kglob
WHERE kglhdadr=:p1raw;
Find the sessions that are waiting/blocking on that object.
SELECT 'alter system kill session ' || '''' || s.SID || ', '|| s.serial# || '''' || ' immediate;' kill_session_sql, 
kglpnmod "Mode Held", kglpnreq "Request" 
FROM sys.x$kglpn p, sys.v_$session s 
WHERE p.kglpnuse = s.saddr AND kglpnhdl = :P1RAW 
--AND kglpnreq=0;
Kill the session with Request=0, Mode Held=3 or 2