脚本查找"library cache lock"阻塞的会话

以sys用户登录

—找出library cache lock对应的sid

select sid,saddr from v$session where event= 'library cache lock';

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

—根据saddr查找请求（被阻塞）的会话以及对象

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

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

—根据saddr查找阻塞的会话以及对象

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-blocking sid  62d064dc   3    EMPLOYEES  

—查找阻塞会话的sid以及客户端信息

select sid,username,terminal,program from v$session where saddr = '572eac94'

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

—根据阻塞会话的sid查找所有被阻塞的会话

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)
);