The release script gets to its third statement and stops:

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

You run it again. Same error. The table isn’t busy in any way you can see: nothing is running against it. The application is quiet, and the database is fine.

Somewhere there’s a session with an uncommitted change to that table. It might be one row, changed an hour ago, by an application that forgot to commit, a SQL Developer window someone left open, or a pooled connection with an open transaction. That session isn’t doing anything. It’s just holding its lock, and that’s enough.

This post covers what the error means, how to find that session when the message doesn’t name it, and three things that surprise people: some DDL doesn’t fail at all, it waits; DDL_LOCK_TIMEOUT makes every other writer wait too; and on some databases the waiting kind fails again. It ends with a lab that proves each step on Oracle Database Free 26ai.

What the error means

DML takes a table-level lock (a TM lock) for as long as its transaction is open. It’s a shared mode: any number of sessions can update different rows of the same table at once. It’s released at commit or rollback, not when the statement finishes.

Most DDL on a table needs that table to itself, so it needs every transaction on the table to end first. By default it doesn’t wait: DDL_LOCK_TIMEOUT is 0, which means “no wait”, and the statement fails at once with ORA-00054. One uncommitted row is enough. So is a SELECT ... FOR UPDATE that was never committed.

On 26ai the message says more than it used to:

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 73205 ("Table") and 0
("operation")

The number is the table’s OBJECT_ID. In the lab it matched DBA_OBJECTS.OBJECT_ID for the table every time. The message doesn’t name the session holding the lock. On 21c (XE 21.3, in a hand run) the message was only the classic text, with no object number at all.

Find the session

The usual first query, BLOCKING_SESSION in V$SESSION, finds nothing here. That column is filled in for a session that is waiting, and a DDL that failed at once isn’t waiting. In the lab, while the blocker held its lock and the TRUNCATE had already failed, no session had BLOCKING_SESSION set.

Ask the lock itself instead. V$LOCKED_OBJECT lists every session holding a lock on an object:

SELECT lo.session_id AS sid, s.serial#, s.username, s.status, s.machine, s.program,
       lo.locked_mode, t.start_time AS transaction_started,
       ROUND(s.last_call_et / 60) AS idle_minutes
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
LEFT JOIN v$transaction t ON t.addr = s.taddr
WHERE  o.owner = 'APP' AND o.object_name = 'ORDERS';   -- or: lo.object_id = 73205, from the message

In the lab it returned exactly one row: the blocker, INACTIVE, LOCKED_MODE 3 (row exclusive, an open DML transaction), with a transaction start time. That’s the shape to look for: a session that isn’t running anything, with a transaction that started a while ago. MACHINE and PROGRAM tell you whose it is.

On RAC, query GV$LOCKED_OBJECT and GV$SESSION and join on INST_ID too; the blocker can be on another instance.

Then decide what to do with it

You have two options, and they’re not equal:

• Ask the owner to commit or roll back. If it’s a person with a forgotten window, this is the clean option.
• End the session. ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE. In the lab the TRUNCATE then ran at once, and the blocker’s uncommitted change was gone: killing a session rolls its transaction back. If that was a real business change that someone thought was saved, it isn’t. Find out what it was before you kill it.

If it’s an application connection, ending it fixes tonight’s deploy, but the code path that leaves transactions open is still there. Sessions orphaned by a dropped connection can hold the same lock, with nobody left to commit.

Not all DDL fails: some of it waits

Oracle splits DDL into two kinds. Both wait for the transactions already open on the table. They differ in what happens to everyone else while they wait:

• Blocking DDL makes new DML on the table wait behind it, and gives up with ORA-00054 when DDL_LOCK_TIMEOUT runs out (at once, with the default of 0). TRUNCATE, DROP, CREATE INDEX, MOVE, MODIFY and DROP COLUMN are blocking.
• Nonblocking DDL lets new DML run, and ignores DDL_LOCK_TIMEOUT. It waits as long as the transaction stays open. The ONLINE operations are nonblocking (CREATE INDEX ... ONLINE, ALTER INDEX ... REBUILD ONLINE, ALTER TABLE ... MOVE ONLINE), and so, since release 23.3, is a plain ALTER TABLE ... ADD column.

Both behaviors are documented: the Development Guide explains the two kinds, and the SQL Language Reference lists the nonblocking statements by release. Here is what 26ai did when I ran each statement by hand against one idle session holding one uncommitted row, with DDL_LOCK_TIMEOUT at 0. The lab asserts the TRUNCATE, CREATE INDEX, ADD column, CREATE INDEX ... ONLINE and DROP COLUMN cases on every run:

Statement	Result
TRUNCATE TABLE, DROP TABLE, LOCK TABLE ... NOWAIT	ORA-00054 at once
CREATE INDEX, ALTER INDEX ... REBUILD, DROP INDEX	ORA-00054 at once
ALTER TABLE ... MOVE, MODIFY, RENAME COLUMN, DROP COLUMN	ORA-00054 at once
ADD CONSTRAINT ... CHECK (validated), DROP PRIMARY KEY	ORA-00054 at once
ALTER TABLE ... ADD a column (nullable, with a default, or NOT NULL with a default)	waited for the commit, then ran
CREATE INDEX ... ONLINE, REBUILD ONLINE, DROP INDEX ... ONLINE, MOVE ONLINE	waited for the commit, then ran
SET UNUSED COLUMN ... ONLINE, ADD CONSTRAINT ... ENABLE NOVALIDATE	waited for the commit, then ran
COMMENT ON TABLE, GRANT	ran at once: no table lock needed

(I also ran the ADD column and CREATE INDEX ... ONLINE cases once by hand on the gvenzl/oracle-xe:21-slim image, XE 21.3, and on Free 23.5. Both waited there too, and TRUNCATE failed at once; only the message text differed. So the plain ADD column waited on 21c even though the documentation lists it under 23.3. Don’t treat the release number as the boundary: test the statement on your own version.)

The waiting kind is gentler on the application: in the lab, while ADD and CREATE INDEX ... ONLINE waited, another session’s UPDATE on the table ran at once. But it has its own failure mode. With DDL_LOCK_TIMEOUT = 5, CREATE INDEX gave up after 5 seconds, as asked, while ADD and CREATE INDEX ... ONLINE ignored the timeout and waited the full 28 seconds until the blocker committed. If the blocker never commits, they never finish. A deploy that hangs at an ADD column for an hour has met an idle transaction, not a slow statement.

While it waits, the waiting DDL does show up in V$SESSION: event enq: TX - row lock contention, with the blocker in BLOCKING_SESSION. That’s the one case where the usual query works.

⤢ Enlarge

DDL_LOCK_TIMEOUT makes everyone else wait too

The common advice for ORA-00054 is to set DDL_LOCK_TIMEOUT so the DDL waits instead of failing:

ALTER SESSION SET ddl_lock_timeout = 30;

For blocking DDL, that wait has a cost the advice usually leaves out. While a blocking DDL waits for its lock, new DML on the table queues behind it. Not just DML on the blocker’s rows: any row.

The lab ran ALTER TABLE ... DROP COLUMN with DDL_LOCK_TIMEOUT = 30 while the blocker kept its row for 45 seconds. A third session then updated a different row, one nobody had touched. It waited 27 seconds: BLOCKING_SESSION pointed at the DDL, and FINAL_BLOCKING_SESSION at the idle session. Then the DDL timed out with ORA-00054 anyway, and the update ran.

So the timeout turned one failed deploy statement into 27 seconds of stalled writes for the application, and the deploy still failed. On a busy table, every write that arrives during the wait stalls the same way.

The parameter is still useful. Keep it short, a few seconds, so the stall is short too, and retry the DDL a few times rather than waiting long once. Better still, check for open transactions on the table before running it.

Supplemental logging brings the error back

There’s a catch in the nonblocking list: some nonblocking DDL is downgraded to blocking when supplemental logging is on. Supplemental logging is what GoldenGate, LogMiner-based capture tools and logical standby need, so it’s on in exactly the databases where replication makes deploys delicate.

The lab turned on minimal database-level supplemental logging (ALTER DATABASE ADD SUPPLEMENTAL LOG DATA) and ran the same ADD column against the same idle session. This time it failed at once with ORA-00054. CREATE INDEX ... ONLINE still waited.

So the same deploy script can wait politely in test and fail in production, if only production feeds a replication tool. Check before you rely on the waiting behavior:

SELECT supplemental_log_data_min FROM v$database;

A safer way to run DDL on a live table

1. Look first. Run the V$LOCKED_OBJECT query for the tables the release touches. Any session holding a lock on them will make the DDL fail, or wait.
2. Prefer the online forms where they exist: CREATE INDEX ... ONLINE, REBUILD ONLINE, MOVE ONLINE. They don’t stall the application while they wait.
3. Put a time limit on the deploy step, not just on the DDL. Nonblocking DDL ignores DDL_LOCK_TIMEOUT, so a statement that waits on a forgotten transaction will wait until someone notices. Watch for the DDL session in enq: TX - row lock contention with a BLOCKING_SESSION.
4. For blocking DDL, keep DDL_LOCK_TIMEOUT short and retry, so any stall it causes is seconds, not minutes.

Prove it

The lab runs Oracle Database Free in a container. The blocker is a SQL*Plus session that updates one row of APP.ORDERS and sits idle without committing, then six drills run against it:

drill    case                                                 result
busy     TRUNCATE                                             ORA-00054 at once; message has ORDERS' OBJECT_ID, no session
find     V$LOCKED_OBJECT for that object                      the blocker: INACTIVE, mode 3, open transaction;
                                                              BLOCKING_SESSION empty everywhere
kinds    CREATE INDEX, DDL_LOCK_TIMEOUT 5                     ORA-00054 after 5 s
         ADD column, same timeout                             no error: waited ~28 s for the commit; other DML ran
         CREATE INDEX ONLINE, same timeout                    the same
queue    DROP COLUMN, DDL_LOCK_TIMEOUT 30, blocker holds 45 s an update of another row waited 27 s behind it;
                                                              the DDL still failed with ORA-00054
suplog   ADD column with supplemental logging on              ORA-00054 at once; CREATE INDEX ONLINE still waits
fix      kill the blocker                                     TRUNCATE runs; the blocker's change is rolled back

Don’t take my word for it — run it. The ddl-lock-busy lab asserts every row: the immediate ORA-00054 and the object number in the message; V$LOCKED_OBJECT finding exactly the blocker, idle, with an open transaction, while no session has BLOCKING_SESSION set; CREATE INDEX giving up at the timeout while ADD and CREATE INDEX ... ONLINE wait past it with the blocker in BLOCKING_SESSION and other DML unaffected; the update of an untouched row stuck 20 seconds or more behind a waiting DROP COLUMN; ADD failing again under supplemental logging; and the kill rolling the change back. If any of that doesn’t reproduce, the run fails. Proven on every CI push.

What teams get wrong

• Looking for a busy session. The blocker is usually idle. Look for an open transaction, not activity.
• Relying on BLOCKING_SESSION. It’s empty when the DDL failed at once. Use V$LOCKED_OBJECT.
• Setting a long DDL_LOCK_TIMEOUT. For blocking DDL, every write to the table waits behind it for as long as it waits.
• Assuming ADD column always fails fast. On 26ai (and on 21c and 23.5 in a hand run) it waited for the commit, and DDL_LOCK_TIMEOUT didn’t stop it. A deploy can hang there indefinitely.
• Testing the deploy on a database without supplemental logging. With it on, the nonblocking ADD that waited in test fails in production.
• Killing the session without asking what it was doing. Its uncommitted work is rolled back.

Frequently asked questions

What does ORA-00054 resource busy and acquire with NOWAIT specified or timeout expired mean?

A DDL statement (or a LOCK TABLE ... NOWAIT) needed a lock on a table that another session holds, and didn't get it in time. Usually the other session has an uncommitted change to the table: even one changed row holds a table-level lock until the transaction commits or rolls back. With the default DDL_LOCK_TIMEOUT of 0 the statement fails at once. On Oracle Database 23ai and 26ai the message reads 'Failed to acquire a lock' and includes the table's object number.

How do I find which session is causing ORA-00054?

Query V$LOCKED_OBJECT for the table (by owner and name through DBA_OBJECTS, or by the object number in the 26ai message) and join to V$SESSION and V$TRANSACTION. The blocker is usually an INACTIVE session with an open transaction and LOCKED_MODE 3. BLOCKING_SESSION in V$SESSION doesn't help when the DDL failed at once, because nothing is waiting. In the companion lab, V$LOCKED_OBJECT returned exactly the blocking session while no session had BLOCKING_SESSION set.

Does DDL_LOCK_TIMEOUT fix ORA-00054?

It makes blocking DDL wait for its lock instead of failing at once, but while it waits, new DML on the table queues behind it. In the companion lab a DROP COLUMN with DDL_LOCK_TIMEOUT = 30 made another session's update of an unrelated row wait 27 seconds, and the DDL still failed with ORA-00054. It also has no effect on nonblocking DDL such as CREATE INDEX ONLINE or, since 23.3, ALTER TABLE ADD column. Keep it short and retry, and find the session holding the lock.

Why does ALTER TABLE ADD COLUMN hang instead of failing with ORA-00054?

Oracle lists ALTER TABLE ... ADD column as nonblocking DDL from release 23.3 (and in a hand run on XE 21.3 it already behaved that way): it waits for open transactions on the table to finish while letting new DML run, and it ignores DDL_LOCK_TIMEOUT. In the companion lab on 26ai it waited until the blocking session committed. If that session never commits, the ALTER never finishes. The waiting session shows the event 'enq: TX - row lock contention' with the blocker in BLOCKING_SESSION.

Does CREATE INDEX ONLINE avoid ORA-00054?

Yes: CREATE INDEX ... ONLINE is nonblocking DDL, so it doesn't fail with ORA-00054 because of an open transaction. Instead it waits for that transaction to end, and lets other DML on the table run meanwhile. In the companion lab it waited about 28 seconds until the blocker committed, even with DDL_LOCK_TIMEOUT set to 5, and kept waiting with supplemental logging on.

Why does ADD COLUMN fail with ORA-00054 on one database but not another?

Check supplemental logging. Oracle downgrades some nonblocking DDL to blocking when supplemental logging is on, which it is on databases that feed GoldenGate, LogMiner-based tools or a logical standby. In the companion lab, with minimal database-level supplemental logging turned on, ALTER TABLE ADD failed at once with ORA-00054 against the same idle session it had waited for without it.

ORA-00054 is almost never a busy table. It’s one idle session with an open transaction. Find it with V$LOCKED_OBJECT, decide whether it should commit or be killed, and know which of your release statements will fail fast, which will wait, and which will make the application wait with them. Then prove it with the ddl-lock-busy lab, where one uncommitted row fails a TRUNCATE, stalls an unrelated update for 27 seconds, and holds an ADD column until it commits.