--查看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/