Oracle Error ORA-00054

Error code

ORA-00054

Product

Oracle Database

Versions

Oracle Database 19c, 21c, 23ai and 26ai (wording differs: see below)

Category

Locking, Transactions & Sessions

Last reviewed

24 October 2026

What it means

ORA-00054 means your statement needed a lock that another session holds and was not allowed to wait for it (NOWAIT, or a lock timeout that ran out), so Oracle returned at once instead of queueing.

Most Oracle statements simply wait when another session holds a lock they need. ORA-00054 appears when waiting is not allowed or the allowed wait has run out: LOCK TABLE ... NOWAIT, SELECT ... FOR UPDATE NOWAIT, and DDL such as ALTER TABLE, DROP TABLE or TRUNCATE on a table with uncommitted changes. For DDL the wait is set by the DDL_LOCK_TIMEOUT parameter, whose default of 0 means NOWAIT.

Current releases rewrite the message to describe the lock: its type and name (TM is the DML lock on a table), and two identifiers. In Oracle's own example the first identifier, described as "Table", is the object number of the locked table. Oracle's reference gives a query on GV$SESSION and GV$LOCK that uses these values to find the session holding the lock and how many seconds it has held it. Oracle Database 19c and 21c print only "resource busy and acquire with NOWAIT specified or timeout expired".

The holder is usually a session with an uncommitted transaction on the table: a developer's SQL tool with an unfinished UPDATE, a batch job, or an application connection that never committed. Once it commits or rolls back, the lock is released and your statement will succeed.

Common causes

• DDL (ALTER TABLE, DROP TABLE, TRUNCATE, CREATE INDEX without ONLINE) on a table another session has changed but not committed, with DDL_LOCK_TIMEOUT at its default of 0.
• SELECT ... FOR UPDATE NOWAIT on rows that another transaction has locked.
• LOCK TABLE ... NOWAIT on a table that another session holds a conflicting lock on.
• A SQL tool session (SQL Developer, SQL*Plus) left with uncommitted changes, holding locks for a long time.
• A deployment script that runs DDL while the application is still writing to the table.

Diagnose it

Read-only These queries only read the data dictionary (USER_, ALL_ and DBA_ views) and dynamic performance views (V$). They do not change data, privileges or settings, so they are safe to run first. DBA_ and V$ views need the matching privileges.

SELECT owner, object_name, object_type
FROM dba_objects
WHERE object_id = 74855;  -- the first identifier from the message

SELECT s.sid, s.serial#, s.username, s.status, s.osuser, s.machine,
       s.module, s.program, s.logon_time, lo.locked_mode
FROM v$locked_object lo
JOIN dba_objects o ON o.object_id = lo.object_id
JOIN v$session s ON s.sid = lo.session_id
WHERE o.owner = 'SHOP'
  AND o.object_name = 'ORDERS';

SELECT sid, username, blocking_session, event, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

-- run in the session that holds the lock
COMMIT;    -- or ROLLBACK, if the changes are not wanted

ALTER SESSION SET ddl_lock_timeout = 60;

ALTER TABLE shop.orders ADD (notes VARCHAR2(400));

SELECT id, status
FROM shop.orders
WHERE id = :order_id
FOR UPDATE WAIT 5;

ALTER SYSTEM KILL SESSION '147,41814';

Examples

Altering a table while another session has an uncommitted change

Not reproduced here: no Oracle Database is installed for this site. The result shows the current message format from Oracle's error reference, whose own example is a DROP TABLE blocked by a TM lock; the object number 74855 is illustrative and differs on every database. Oracle Database 19c prints "resource busy and acquire with NOWAIT specified or timeout expired".

-- session 1 (not committed)
UPDATE orders SET status = 'SHIPPED' WHERE id = 42;

-- session 2
ALTER TABLE orders ADD (notes VARCHAR2(400));

ORA-00054: Failed to acquire a lock (Type: "TM", Name: "DML", Description: "Synchronizes accesses to an object") because it is currently held by another session. The resource being locked can be identified by 74855 ("Table") and 0 ("operation")

SELECT FOR UPDATE NOWAIT on a row another transaction holds

Not reproduced here: no Oracle Database is installed for this site. The result shows the 19c wording from Oracle's error reference. Current releases print the longer "Failed to acquire a lock" form, describing the lock type and its identifiers; that exact text for a row lock is not shown in Oracle's reference, so it is not reproduced here.

-- session 1 (not committed)
UPDATE orders SET status = 'PICKING' WHERE id = 42;

-- session 2
SELECT id, status
FROM orders
WHERE id = 42
FOR UPDATE NOWAIT;

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

How to prevent it

Run DDL in a maintenance window or with a session DDL_LOCK_TIMEOUT, and check V$LOCKED_OBJECT for open transactions on the table before you start. Commit or roll back in SQL tools as soon as you finish a change, rather than leaving an open transaction in a window you are no longer using.

In application code, use NOWAIT or WAIT n only where you handle ORA-00054 deliberately, for example by skipping a row another worker has already claimed, and keep transactions short so locks are released quickly.

Common questions

Learn more on CodeWithSQL

• Transactions, locks and deadlocks
• SQL transactions and ACID explained

Official documentation

• ORA-00054 (Oracle Database Error Messages)
• DDL_LOCK_TIMEOUT (Oracle Database Reference 19c)
• V$LOCKED_OBJECT (Oracle Database Reference 19c)
• LOCK TABLE (Oracle SQL Language Reference 19c)

Last technically reviewed 24 October 2026; examples and diagnostics run on Oracle Database Error Messages reference (26ai, 21c and 19c); not run locally. Messages and explanations follow Oracle's Database Error Messages reference; no Oracle Database was available, so the examples were not run locally.