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

2 comments:

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