library cache lock/pin 检测

--查看LOCK信息
select xidusn, object_id, session_id, locked_mode from v$locked_object;

--查看等待事件
select event,sid,p1,p2,p3 from v$session_wait where event not like 'SQL*%' and event not like 'rdbms%';

P1 是句柄地址(handle address),也就是'library cache lock'发生的地址。
P2 是一个状态对象，在这里，它表示在对象上加载的锁的地址(lock address)。
P1 和 P2都是科学计数发表示的10进制数。

--转化handle address 至10进制 和上面的P1 进行对比
select to_number('C000000122E2A6D8','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') from dual;

--根据handle address 可以得到library cache 中相应的锁信息
select INST_ID,USER_NAME,KGLNAOBJ,KGLLKSNM,KGLLKUSE,KGLLKSES,KGLLKMOD,KGLLKREQ,KGLLKPNS,KGLLKHDL from X$KGLLK where KGLLKHDL = 'C000000122E2A6D8' order by KGLLKSNM,KGLNAOBJ

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--检查library cache pin (session)
SELECT a.SID,
       a.username,
       a.program,
       b.addr,
       b.kglpnadr,
       b.kglpnuse,
       b.kglpnses,
       b.kglpnhdl,
       b.kglpnlck,
       b.kglpnmod,
       b.kglpnreq
  FROM v$session a, x$kglpn b
 WHERE     a.saddr = b.kglpnuse
       AND b.kglpnmod <> 0
       AND b.kglpnhdl IN (SELECT p1raw
                            FROM v$session_wait
                           WHERE event LIKE 'library%')
                          
--获得等待对象
SELECT addr,
       kglhdadr,
       kglhdpar,
       kglnaown,
       kglnaobj,
       kglnahsh,
       kglhdobj
  FROM x$kglob
 WHERE kglhdadr IN (SELECT p1raw
                      FROM v$session_wait
                     WHERE event LIKE 'library%')

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15412087/viewspace-1777314/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15412087/viewspace-1777314/