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;

SQL> select to_name from v$object_dependency where to_address = '0700010AA444F6D8';
TO_NAME

------------

TEST

— To check the blocked Session (Waiter)

SQL> select distinct kglnaobj from x$kgllk  where kgllkuse in (select saddr from v$session where sid = 1529);

KGLNAOBJ

------------------------------------------------------------

select decode(upper(failover_method), NULL, 0 , 'BASIC', 1,

select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U

TEST

 SELECT VALUE$ FROM SYS.PROPS$ WHERE NAME = 'OGG_TRIGGER_OPT

DATABASE

ALTER SESSION SET TIME_ZONE='+02:00'

alter procedure user1.test compile

8 rows selected.

— To check the Holding Session (Waiter)

SQL> select distinct kglnaobj from x$kgllk  where kgllkuse in (select saddr from v$session where sid =410); 

KGLNAOBJ

------------------------------------------------------------

TEST

DATABASE

USER1

DBMS_APPLICATION_INFO

update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0

BEGIN user1.test; END;


6 rows selected.

SQL> select sid,serial# from v$session where sid=410;

       SID    SERIAL#

---------- ----------

       410      30563

SQL> alter system kill session '410,30563';

System altered.

Today’s thought 

“Keep your face always toward the sunshine—and shadows will fall behind you.” —Walt Whitman

Thank you for giving your valuable time to read the above information.

For More Detail , You can join us follow:

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp