尋找 library cache lock 等待事件的session

library cache lock

select * from v$session_wait where event<>'SQL*Net message from client'

在v$session_wait 裡查到library cache lock 的saddr,將saddr傳給下一個查詢的kgllkses

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 = '0700000A877A66D8' /* blocked session */
     and lock_a.kgllkhdl = lock_b.kgllkhdl
     and kgllkreq > 0);
    

查處具體的saddr到v$session檢視查出sid和serial#並殺之
select * from v$session where saddr='0700000A87814228'
alter system kill session '2720,41211'

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16400082/viewspace-746344/，如需轉載，請註明出處，否則將追究法律責任。