How to Fix ORA-00054: Resource Busy and Acquire with NOWAIT Specified or Timeout Expired

While working with Oracle databases, you might encounter:

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

If you’re wondering:

Why does this error occur?
What action should I take to fix it?
How do I avoid it in the future?

This post will clarify these questions simply so you can handle this confidently in your DBA workflow.

What is ORA-00054?

This error indicates that:

You tried to acquire a lock on a table or row,
NOWAIT or a timeout was specified,
But the resource is already locked by another session, so Oracle cannot acquire the lock immediately.

This typically happens when you:

Run ALTER TABLE or DROP TABLE on a table that is currently in use.
Execute DDL statements on objects locked by active transactions.
Attempt DML with SELECT FOR UPDATE NOWAIT while another session is holding the lock.

Why does it occur?

Oracle uses locks to maintain data consistency and concurrency control. If you request a lock with NOWAIT, Oracle will not wait for the resource to become available and throws ORA-00054 if it is already locked.

For example:

ALTER TABLE employees ADD (new_column NUMBER);

will fail with ORA-00054 if:

Another session is updating employees.
A long-running transaction is holding a lock on employees.

What action is needed?

1️⃣ Identify the locking session

Run:

SELECT 
    l.session_id, 
    s.serial#, 
    s.username, 
    s.machine, 
    s.program 
FROM 
    v$locked_object l, dba_objects o, v$session s 
WHERE 
    l.object_id = o.object_id 
    AND l.session_id = s.sid 
    AND o.object_name = 'EMPLOYEES';

Replace 'EMPLOYEES' with your table name.

This will show who is holding the lock on your object.

2️⃣ Wait or Kill the session (with caution)

If the locking session is running a valid transaction:

Wait for it to complete before retrying your operation.

If it is blocking and you need to proceed:

Check with your team and, if safe, you can kill the session:

ALTER SYSTEM KILL SESSION 'sid,serial#';

⚠️ Use this with extreme caution in production, ensuring you are not interrupting critical transactions.

3️⃣ Retry without NOWAIT

If your statement used NOWAIT, consider removing it to allow Oracle to wait until the lock is released:

SELECT * FROM employees FOR UPDATE;

instead of

SELECT * FROM employees FOR UPDATE NOWAIT;

Or handle it in application logic to retry after waiting.

4️⃣ Schedule DDL during maintenance windows

DDL operations should ideally be performed during low-activity windows to avoid conflicts with active transactions.

How to Avoid ORA-00054 in the Future?

Schedule DDL changes during maintenance or periods of low user activity.
Use DBMS_LOCK or application logic to manage custom locking and avoid clashes.
Monitor sessions with scripts/tools before running DDL.
Train your team to communicate active locking operations before large changes.

Summary

Why it occurs: Another session is holding a lock when you requested NOWAIT or a timeout expired.
What to do: Identify the blocking session, wait, kill if necessary, or retry without NOWAIT.
Prevention: Schedule DDL carefully and monitor active sessions.

By understanding ORA-00054 and how to handle it systematically, you will avoid unnecessary production disruptions and strengthen your Oracle administration confidence.