🧩 ORA-00054: Resource Busy and Acquire with NOWAIT Specified

🧩 Introduction

The ORA-00054 error is a common Oracle database issue that occurs when a session tries to access a resource (like a table) that is currently locked by another session. This usually happens during DDL operations such as ALTER, DROP, or TRUNCATE, when another transaction is using the same object. Understanding how to identify and resolve this lock contention is key to maintaining smooth database operations.

Error Message Example:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Causes

This error indicates that Oracle tried to obtain a lock on a resource but failed because:

Another session is currently locking the same object.

The operation used the NOWAIT clause, which prevents it from waiting for the resource to be free.

A transaction is running that hasn’t yet committed or rolled back, holding a lock on the table.

Common Scenarios

Running ALTER TABLE or DROP TABLE while another session is inserting or updating rows.

Attempting to compile a PL/SQL object that’s in use.

Performing maintenance operations during active transactions.

Step 1: Identify the Locking Session

You can find which session is holding the lock using the query below:

SELECT

    l.session_id AS locking_sid,

    s.serial#,

    s.username,

    s.machine,

    l.locked_mode,

    o.object_name

FROM v$locked_object l

JOIN all_objects o ON l.object_id = o.object_id

JOIN v$session s ON l.session_id = s.sid;

➡️ This query lists all locked objects and the sessions currently holding them.

Step 2: Kill or Wait for the Locking Session

Once you identify the blocking session, you can decide whether to:

Wait for the session to complete naturally, or

Forcefully end it using:

ALTER SYSTEM KILL SESSION 'sid,serial#';

➡️ Be cautious when killing sessions—doing so may rollback ongoing transactions.

Step 3: Retry the Operation Without NOWAIT

If possible, remove the NOWAIT clause so Oracle waits until the resource is free:

ALTER TABLE employees MOVE TABLESPACE users;

or add a delay before retrying the operation in your script or application logic.

Step 4: Preventive Measures

Schedule DDL changes during maintenance windows.

Ensure users commit transactions promptly.

Use application-level locking logic to avoid contention.

Monitor active sessions using v$session and v$lock views regularly.

Conclusion

The ORA-00054 error is a typical concurrency issue that signals resource contention in Oracle. By identifying the blocking session, controlling transaction flow, and planning maintenance operations properly, you can avoid such locking problems and keep your database performing efficiently.