DOYENSYS Knowledge Portal

We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal

Thursday, March 24, 2016

Script to Analyze Library cache timeout.

ORA-04021 timeout occurred while waiting to lock object %s%s%s%s%s.
Cause:  While trying to lock a library object, a time-out occurred.
Action: Retry the operation later.

To find Blocking  Session and Waiting Session, use the following sql.

select /*+ ordered */ w1.sid  waiting_session,
h1.sid  holding_session,
w.kgllktype lock_or_pin,
        w.kgllkhdl address,
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
  from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
  (((h.kgllkmod != 0) and (h.kgllkmod != 1)
     and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
     (((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


Jhony Bairstow said...

The quality of your articles and contents is great. cheap clubflyer

Jhony Bairstow said...

The world manufacture filler of printing inks has been provided in place of production as recovered as revenue. Printing VIP