ORACLE library cache lock查询

ORACLE library cache lock查询

 

select Distinct /*+ ordered */ w1.sid waiting_session, 

h1.sid holding_session, 

w.kgllktype lock_or_pin, 

od.to_owner object_owner, 

od.to_name object_name, 

oc.Type, 

decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 

'Unknown') mode_held, 

decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 

'Unknown') mode_requested, 

xw.KGLNAOBJ wait_sql,xh.KGLNAOBJ hold_sql 

from dba_kgllock w, dba_kgllock h, v$session w1, 

v$session h1,v$object_dependency od,V$DB_OBJECT_CACHE oc,x$kgllk xw,x$kgllk xh 

where 

(((h.kgllkmod != 0) and (h.kgllkmod != 1) 

and ((h.kgllkreq = 0) or (h.kgllkreq = 1))) 

and 

(((w.kgllkmod = 0) or (w.kgllkmod= 1)) 

and ((w.kgllkreq != 0) and (w.kgllkreq != 1)))) 

and w.kgllktype = h.kgllktype 

and w.kgllkhdl = h.kgllkhdl 

and w.kgllkuse = w1.saddr 

and h.kgllkuse = h1.saddr 

And od.to_address = w.kgllkhdl 

And od.to_name=oc.Name 

And od.to_owner=oc.owner 

And w1.sid=xw.KGLLKSNM 

And h1.sid=xh.KGLLKSNM 

And (w1.SQL_ADDRESS=xw.KGLHDPAR And w1.SQL_HASH_VALUE=xw.KGLNAHSH) 

And (h1.SQL_ADDRESS=xh.KGLHDPAR And h1.SQL_HASH_VALUE=xh.KGLNAHSH) 

;