ORA-00054: resource busy and acquire with NOWAIT specified

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

Another session holds a lock on the object or rows you need, and your statement was not allowed to wait for it. Find the blocking session, let it commit or roll back, then run your statement again.

What it means

Some statements need an exclusive lock and refuse to queue for it. DDL such as ALTER TABLE, DROP or TRUNCATE needs exclusive access to the table, and SELECT … FOR UPDATE NOWAIT asks not to wait at all. If another session holds a conflicting lock — usually uncommitted changes — Oracle returns ORA-00054 instead of waiting.

Common causes

Running DDL on a table that another session has changed but not yet committed.
SELECT … FOR UPDATE NOWAIT on rows another session has locked.
A forgotten open transaction in another tool or worksheet.
Online application sessions continuously touching a busy table while you try to change its structure.

How to fix it

Find who holds the lock: query V$LOCKED_OBJECT joined to V$SESSION, or use a session monitor.
Ask the owner of that session to commit or roll back; kill the session only if you are sure it is safe.
For DDL, let your session wait a few seconds for the lock: ALTER SESSION SET ddl_lock_timeout = 30 (Oracle 11g and later).
Schedule structural changes for a quiet window on busy tables.

Example

Who is locking the table?

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

Let DDL wait up to 30 seconds instead of failing at once:

ALTER SESSION SET ddl_lock_timeout = 30;
ALTER TABLE orders ADD (note VARCHAR2(200));

In SQLlightspeed

When a statement fails, SQLlightspeed’s error panel shows the error code with a plain-language explanation and a suggestion for what to do next, together with Oracle’s own message and a link to Oracle’s documentation — for this error and dozens of other common ones.

Try SQLlightspeed free for 30 days

Oracle documentation for ORA-00054. Oracle is a registered trademark of Oracle Corporation. SQLlightspeed is not affiliated with Oracle.