How to Find which Session is Holding a Particular Library Cache Lock

How to Find which Session is Holding a Particular Library Cache Lock (Doc ID 122793.1)

METHOD 2: EXAMINE THE X$KGLLK TABLE

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 sid,saddr from v$session where event= 'library cache lock';

SID      	 SADDR
---------- 	--------
16  		572ed244

select kgllkhdl Handle,kgllkreq Request, kglnaobj Object
from x$kgllk where kgllkses = '572ed244'
and kgllkreq > 0;

HANDLE   REQUEST   OBJECT
-------- ---------- ------------------------------------------------------------
62d064dc          2 EMPLOYEES

This will show you the library cache lock requested by this session (KGLLKREQ > 0) 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 Systemstate Analysis as shown above.

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.The session holding the lock will have KGLLKMOD > 0 as it is holding the lock.

select kgllkses saddr,kgllkhdl handle,kgllkmod mod,kglnaobj object
from x$kgllk lock_a
where kgllkmod > 0
and exists (select lock_b.kgllkhdl from x$kgllk lock_b
where kgllkses = '572ed244' /* blocked session */
and lock_a.kgllkhdl = lock_b.kgllkhdl
and kgllkreq > 0);

SADDR     			HANDLE   	MOD 							OBJECT
--------  		--------	 ----------    ------------------------------------------------------------
572eac94		  62d064dc          3  	   EMPLOYEES

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 = '572eac94'

	SID      		  USERNAME                			  TERMINAL 							PROGRAM
---------- ------------------------------ ------------------------------ ------------------------------------------------
	12       	   UserName                       	   pts/20 			   sqlplus@xxxxxx.forgotten.realms (TNS V1-V3)

In the same way, we can also find all the blocked sessions:

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 = '572eac94' /* blocking session */
             and lock_a.kgllkhdl = lock_b.kgllkhdl
             and kgllkreq = 0)
);

	SID        USERNAME                       TERMINAL               PROGRAM
----------- -------------------  -------------------------------	----------------------------------
	13         UserName                           abc/22               sqlplus@go.there.com (TNS V1-V3)
	16         UserName                           abc/7                sqlplus@go.there.com (TNS V1-V3)