Ora 04021 ошибка

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

The library cache, which is part of the shared pool in the Oracle instance, contains the definition of different objects, cursors, indexes, tables, functions, packages. When these objects are in use, they cannot be changed.

 If someone is updating a table, you cannot change the definition of the table.
If someone is using a procedure, you cannot compile the procedure.
If someone is using an index, you cannot drop the index.

CAUSE

The ORA-04021 timeout error is most likely caused by another session locking/using a package that you are attempting to execute.

Why can’t you alter these objects, while in use?

These objects are locked in the library cache, by a mechanism based on library locks and library pins.
When a user wants to use an object, the session must obtain a library cache lock in null, shared, or exclusive mode on the objects, depending on the operation.

If you want to drop a table, you need an exclusive lock. This lock will prevent other sessions from executing the same operation, or other disruptive operations (dropping the object).

After the lock is acquired on the object, the session also must obtain a pin in a specific mode: null, shared, exclusive, depending on the operation.

 A session that is waiting on an object already locked/pinned in the library cache, that is incompatible with the requested mode, will wait on events similar to library cache pin or library cache lock, for a limited time, at which point a timeout occurs.

Usually, the timeout occurs after 5 minutes, and the waiting user will receive the ORA-4021 message TIMEOUT OCCURRED WHILE WAITING TO LOCK OBJECT.

You most likely want to troubleshoot and see who is holding a lock on your object. The problem is that the troubleshooting has to happen during the wait period before the ORA-4021 occurs. I recommend that you start investigating when you see that your session is waiting or hanging, and the session cursor is not returned to you right away.

Views that you can use to troubleshoot:

V$ACCESS
DBA_DDL_LOCKS
V$LOCKED_OBJECT

You can start your troubleshooting by checking regular blocking/locking issues. 

SOLUTION

Contact the Oracle team to analyze your situation. As a general workaround, you can stop and start the business events, listeners, clearing cache from Oracle side. Then removed the locks on the tables. You’ll need Oracle DBA help.

One of my readers asked me if I could write about locking and blocking in the database, more specifically about the following error:

ORA-04021: TIMEOUT OCCURRED WHILE WAITING TO LOCK OBJECT

The library cache, which is part of the shared pool in the Oracle instance, contains the definition of different objects, cursors, indexes, tables, functions, packages. When these objects are in use, they cannot be changed.

If someone is updating a table, you cannot change the definition of the table.
If someone is using a procedure, you cannot compile the procedure.
If someone is using an index, you cannot drop the index.

You get the picture.

Why can’t you alter these objects, while in use?

These objects are locked in the library cache, by a mechanism based on library locks and library pins.
When a user wants to use an object, the session has to obtain a library cache lock in null, shared, or exclusive mode on the objects, depending on the operation.
If you want to drop a table, you need an exclusive lock. This lock will prevent other sessions from executing the same operation, or other disruptive operations (dropping the object).

After the lock is aquired on the object, the session also must obtain a pin in a specific mode: null, shared, exclusive, depending on the operation.

A session that is waiting on an object already locked/pinned in the library cache, that is incompatible with the requested mode, will wait on events similar to library cache pin or library cache lock, for a limited time, at which point a timeout occurs.
Usually the timeout occurs after 5 minutes, and the waiting user will receive the ORA-4021 message TIMEOUT OCCURRED WHILE WAITING TO LOCK OBJECT.

You most likely want to troubleshoot and see who is holding a lock on your object. The problem is that the troubleshooting has to happen during the wait period, before the ORA-4021 occurs. I recommend that you start investigating when you see that your session is waiting, or hanging, and the session cursor is not returned to you right away.

Views that you can use to troubleshoot:

V$ACCESS
DBA_DDL_LOCKS
V$LOCKED_OBJECT

You can start your troubleshooting by checking regular blocking/locking issues. I wrote about that in a previous blog post, Who Is Holding The Lock On The Table?

I was not able to reproduce the error in my test environment, the above information is from my research.

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

I have this anonymous PL/SQL block which calculates and prints a value return from a table.

DECLARE
    U_ID NUMBER :=39;
    RETAIL BINARY_FLOAT:=1;
    FLAG NUMBER;
BEGIN
    SELECT NVL(RETAIL_AMOUNT,1),UNIT_ID INTO RETAIL, FLAG FROM UNITS WHERE UNIT_ID=U_ID;
    LOOP
    SELECT NVL(MAX(UNIT_ID),U_ID) INTO FLAG FROM  UNITS WHERE FATHER_ID=FLAG;
    IF FLAG=U_ID THEN EXIT; END IF;
    SELECT RETAIL* RETAIL_AMOUNT INTO RETAIL FROM UNITS WHERE UNIT_ID=FLAG;
    EXIT WHEN FLAG=U_ID;
    END LOOP;
DBMS_OUTPUT.PUT_LINE( RETAIL);
END;

This block work correctly, but I wanted to do the same thing using a PL/SQL Function

I wrote the function as follow:

CREATE OR REPLACE FUNCTION GET_UNIT_RETAIL(U_ID NUMBER)
     RETURN NUMBER
IS
    RETAIL BINARY_FLOAT:=1;
    FLAG NUMBER;
BEGIN
    SELECT NVL(RETAIL_AMOUNT,1),UNIT_ID 
    INTO RETAIL, FLAG 
    FROM UNITS 
    WHERE UNIT_ID=U_ID;

    LOOP
        SELECT NVL(MAX(UNIT_ID),U_ID) 
        INTO FLAG 
        FROM  UNITS 
        WHERE FATHER_ID=FLAG;
        IF FLAG=U_ID THEN 
            EXIT; 
        END IF;
        SELECT RETAIL* RETAIL_AMOUNT 
        INTO RETAIL 
        FROM UNITS 
        WHERE UNIT_ID=FLAG;
        EXIT WHEN FLAG=U_ID;
    END LOOP;

    RETURN NUMBER;
END;
/

When I try to execute the above code to save the function to the database, the environment (SQL*PLUS) hangs for a long time and at the end returns this error:

ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object

What is the problem ??? Please !