Solved – How to fix ORA-00054 resource busy and acquire with NOWAIT specified or timeout expired?

Question: What is the cause of the Oracle error “ORA-00054 resource busy and acquire with NOWAIT specified or timeout expired”?

The error is raised when the following statement is executed against the Oracle Database.

    SQL> ALTER TABLE vst.account ADD (update_date varchar2(100));
    *
    ERROR at line 1:
    ORA-00054: resource busy and acquire with NOWAIT specified

Answer: The Oracle error utility oerr shows this description for the ORA error.

    00054, 00000, “resource busy and acquire with NOWAIT specified or timeout expired”
    Interested resource is busy.

    Retry if necessary or increase timeout.

Cause: The statement requires an exclusive lock on the table.

If the table is active and has DML or DDL transactions against it the transaction will not be able to acquire the lock quickly. This will cause the transaction to fail and return the resource busy error to the session.

Solution

There are three workarounds to address this.

Using DDL_LOCK_TIMEOUT

You can increased the timeout by modifying the DDL_LOCK_TIMEOUT parameter in your session. Once this is set Oracle will wait for the new TIMEOUT before returning the “ORA-00054: resource busy and acquire with NOWAIT specified” error.

    SQL> ALTER SESSION SET ddl_lock_timeout=900;
    Session altered.

    SQL> ALTER TABLE vst.account ADD (update_date varchar2(100));
    Table altered.

Using a LOOP

    CREATE PROCEDURE ddl_loop(run_sql varchar2)
    AS
    timeout_except EXCEPTION;
    PRAGMA exception_init(in_use, -54);
    BEGIN
    WHILE true LOOP
    BEGIN
    EXECUTE IMMEDIATE run_sql;
    EXCEPTION
    WHEN in_use THEN null;
    END;
    dbms_lock.sleep(0.01);
    END LOOP;
    LOOP;

Kill Session Or Shutdown Application

Who is holding the lock? To determine what Session and SQL is operating against the object, run a query against the v$access and the v$session tables to obtain this information.

    SELECT a.object, a.type, a.sid,
    s.serial#, s.username,
    s.program, s.logon_time
    FROM v$access a, v$session s
    WHERE a.sid = s.sid
    AND a.owner = ‘&owner’
    AND a.object = ‘&object_name’;

After determining the user who is accessing the table, determine if the DML being performed can be stopped temporarily or the session killed.

Related OraTips

RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP – When trying to restore a controlfile I am getting the RMAN-06563 error. I have a backed up the controlfile but the RMAN is not able to see it. Why is this happening?

Proxy User Authentication with Connect Through in Oracle Database – The Proxy authentication mechanism allows a user to login as their own user but land into a different schema without knowing the password for that user. For example the USER_B may want to create an object in the SCHEMA_A or want to use the privileges/roles of the SCHEMA_A user.

Other Articles

Oracle 12c RAC: New Features Oracle has come a long way from its inception of the 9i RAC database to its current 12c version. I remember the days when we had to deal with RAC related issues, ranging from performance to stability which gradually improved as this product matured. Learn more about the new feature now available with RAC.

Connecting Applications from Desktop to your VirtualBox Database If you are trying to connect between VirtualBox Machines or trying to setup the network to communicate from the Desktop to the VirtualBox machines, follow the simple to use instructions here


Note: This Oracle documentation was created for reference for use by Vitalsofttech DBA’s. If you have any questions please post by clicking on the ASK A QUESTION link above.

Leave a Reply

Your email address will not be published.