1.获得Library cache Pin等待对象 select addr,kglhdadr,kglhdpar, kglnaown,kglnaobj,kglnahsh,kslhdobj from x$kglob where kglhdadr in (select p1raw from v$session_wait where event like 'library%') 2.获得持有对象的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%') 3.获得持有对象用户的执行代码 select sql_text from v$sqlarea where (v$sqlarea.address,v$sqlarea.hash_value in ( select sql_address,sql_hash_value from v$session where sid in ( select sid from v$session a, x$kglpn b where a.saddr=b.kglpnuse and b.kglpnmod<>0 and kglpnhdl in ( select p1raw from v$session_wait where event like 'library%')))
select addr,kglhdadr,kglhdpar,
kglnaown,kglnaobj,kglnahsh,kslhdobj
from x$kglob
where kglhdadr in (select p1raw
from v$session_wait
where event like 'library%')
2.获得持有对象的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%')
3.获得持有对象用户的执行代码
select sql_text
from v$sqlarea
where (v$sqlarea.address,v$sqlarea.hash_value
in (
select sql_address,sql_hash_value
from v$session
where sid in (
select sid from v$session a, x$kglpn b
where a.saddr=b.kglpnuse
and b.kglpnmod<>0
and kglpnhdl in (
select p1raw from v$session_wait
where event like 'library%')))
where a.ADDR=b.PADDR and b.SQL_HASH_VALUE=c.HASH_VALUE
and b.USERNAME is not null