Library cache locks
                                                 Last update (2008-03-04 14:31:39)
                                                                                                                   Date added (2008-02-22 12:22:23)

Summary
The X$KGLLK table (accessible only as SYS/INTERNAL) contains all the library object locks (both held & requested) for all sessions and is more complete than the V$LOCK view although the column names don't always reveal their meaning.

You can examine the locks requested (and held) by the waiting session by looking up the session address (SADDR) in V$SESSION and doing the following select:
SELECT SADDR FROM V$SESSION WHERE SID = 1843; 
where for example 1843 is the session with the library cache lock wait event
select * from x$kgllk where KGLLKSES = 'C0000001AAFD0758'
This will show you all the library locks held by this session where KGLNAOBJ contains the first 80 characters of the name of the object. The value in KGLLKHDL corresponds with the 'handle address' of the object in METHOD 1.

You will see that at least one lock for the session has KGLLKREQ > 0 which means this is a REQUEST for a lock (thus, the session is waiting). If we now match the KGLLKHDL with the handles of other sessions in X$KGLLK that should give us the address of the blocking session since KGLLKREQ=0 for this session, meaning it HAS the lock.
SELECT * FROM X$KGLLK LOCK_A  
 WHERE KGLLKREQ = 0 
   AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B 
               WHERE KGLLKSES = 'C0000001A9C32C40' /* BLOCKED SESSION */ 
              AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL 
               AND KGLLKREQ > 0);
If we look a bit further we can then again match KGLLKSES with SADDR in v$session to find further information on the blocking session:
SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION 
 WHERE SADDR in  
  (SELECT KGLLKSES FROM X$KGLLK LOCK_A  
   WHERE KGLLKREQ = 0 
     AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B 
                 WHERE KGLLKSES = 'C0000001A9C32C40' /* BLOCKED SESSION */ 
                 AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL 
                 AND KGLLKREQ > 0));
In the same way we can also find all the blocking sessions:
SELECT USERNAME, SID,SERIAL#,USERNAME,TERMINAL,PROGRAM, MODULE, 
'alter system kill session ' || '''' || SID || ', ' || SERIAL# || '''' || ' immediate;' KILL_SQL 
FROM V$SESSION 
WHERE SADDR IN  
  (SELECT KGLLKSES FROM X$KGLLK LOCK_A  
   WHERE KGLLKREQ > 0 
     AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B 
                 WHERE KGLLKSES IN (SELECT SADDR FROM V$SESSION 
WHERE SID = :SID_WITH_LIBRARY_CACHE_LOCK) 
                 AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL 
                 AND KGLLKREQ = 0));
Reviews
Categories
Filters
Search