ORA-04021: timeout occurred while waiting to lock object %s%s%s%s%s

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.

How to find out why an ORA-4021 error occurs:

In that example I have created a scenario where ORA-4021 error appear and show how to find out what causing the error.

-- Terminal 1

SQL> show user

USER is "SYS"

SQL> create user user1 identified by 123;

User created.

SQL> grant create session,resource to user1;

Grant succeeded.

SQL> conn user1/123

Connected.

SQL> create or replace procedure test as

begin

while true loop

null;

end loop;

end;

/

Procedure created.

SQL> alter procedure user1.test compile;

Procedure altered.

SQL> exec user1.test;

-- Terminal 2

-- AS sys user

SQL> alter procedure user1.test compile;

alter procedure scott.p1 compile

*

ERROR at line 1:

ORA-04021: timeout occurred while waiting to lock object

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;