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;

 

WAITING_SESSION

HOLDING_SESSION

LOCK

ADDRESS

MODE_HELD

MODE_REQU

1529

410

Pin

0700010AA444F6D8

Share

Exclusive

 

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

select count(*) from (                                 selec

 

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.

Comments