// Oracle error reference

ORA-00054: resource busy and acquire with NOWAIT specified

A statement that won't wait for locks (NOWAIT, DDL, or a lock with a timeout) found the table or row locked by another session's uncommitted work.

Error code
ORA-00054
Category
Transactions and locking
Error message
resource busy and acquire with NOWAIT specified or timeout expired

What it means

ORA-00054 means a lock was requested without waiting, or with a timeout that expired, and another session holds it. DDL such as ALTER TABLE, DROP TABLE and TRUNCATE needs an exclusive lock and fails immediately by default if any session has uncommitted changes on the table. The full 19c message is resource busy and acquire with NOWAIT specified or timeout expired.

Newer releases describe the lock and who holds it: Failed to acquire a lock (Type: ..., Name: ..., Description: ...) because it is currently held by another session.

Common causes

  • Running DDL on a table while another session has uncommitted DML on it, often a forgotten session in a GUI tool.
  • SELECT ... FOR UPDATE NOWAIT on rows locked by another transaction.
  • LOCK TABLE ... NOWAIT.

How to fix it

  1. Find the session holding the lock.

    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';
  2. Have that session commit or roll back. As a last resort, an administrator can end it.

    ALTER SYSTEM KILL SESSION '123,4567' IMMEDIATE;
  3. Let DDL wait for locks instead of failing at once.

    ALTER SESSION SET ddl_lock_timeout = 60;

Related errors

More transactions and locking errors

The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.

// fewer errors, faster fixes

Work on Oracle in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.