ORA-04021: timeout occurred while waiting to lock object
Cause:
While waiting to lock a library object, a timeout is occurred
Action:
Action depends on the root cause. Based on the cause you can choose to kill the session or wait until the other process has finished and release the lock.
Solution:
Find SID for Holding_session using below query and kill that holding SID using alter system kill session( Example: -- Killing the holding session SID. SQL> alter system kill session '7192,40857';
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;
Output
--Check object dependency
SQL> select to_name from v$object_dependency where to_address = '00000011F7306978';
-- To check the blocked Session (Waiter)
select distinct kglnaobj from x$kgllk where
kgllkuse in (select saddr from v$session where sid = 6648);
-- To check the Holding Session (Waiter)
SQL> select distinct kglnaobj from x$kgllk where
kgllkuse in (select saddr from v$session where sid =7192);
-- Killing the holding session SID
SQL> alter system kill session '7192,40857';