ORA-00054: Resource Busy and Acquire with NOWAIT Specified - Root Cause & Resolution Guide

The ORA-00054: resource busy and acquire with NOWAIT specified error occurs when a database session attempts to lock a table, row, or object using a non-blocking lock request (e.g., SELECT ... FOR UPDATE NOWAIT, LOCK TABLE ... IN EXCLUSIVE MODE NOWAIT, or DDL statements like ALTER TABLE ...), but another session holds a conflicting lock.

This guide covers root cause identification, active blocking lock isolation via V$SESSION and V$LOCKED_OBJECT, safe session termination, non-destructive workaround options, and long-term prevention strategies.

Environment & Prerequisites

• Database Versions: Oracle 11g, 12c, 18c, 19c, 21c, 23c/23ai (Single Instance & RAC)

• Target Audience: Oracle DBAs, EBS/Apps DBAs, Database Developers

• Privileges Required: SELECT on V$SESSION, V$LOCKED_OBJECT, V$LOCK, DBA_OBJECTS, and ALTER SYSTEM (if terminating blocking sessions).

Problem Description & Exact Error Stack

A user or automated script executes a DDL operation, direct-path load, or row lock statement, resulting in immediate execution failure.

Command Line Execution Log

SQL

SQL> ALTER TABLE apps.oe_order_lines_all ADD (x_custom_attribute VARCHAR2(50));
ALTER TABLE apps.oe_order_lines_all ADD (x_custom_attribute VARCHAR2(50))
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

PL/SQL Error Log

Plaintext

ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at "APPS.XX_INVENTORY_PKG", line 142

Root Cause Analysis

Oracle DDL statements require an Exclusive DDL Lock (Type TM, Mode 6) on the target object to update the Data Dictionary safely. DML statements (INSERT, UPDATE, DELETE) or explicit SELECT ... FOR UPDATE statements place shared Row Exclusive locks (Type TM, Mode 3) on the object level and exclusive locks (Type TX, Mode 6) on the specific row blocks.

An ORA-00054 is triggered when:

1. Implicit NOWAIT in DDL: DDL operations automatically issue internal lock requests with NOWAIT behavior (or respect DDL_LOCK_TIMEOUT). If an active uncommitted DML session holds a lock on the table, the DDL fails immediately.

2. Explicit NOWAIT in DML/PL/SQL: A query containing FOR UPDATE NOWAIT encounters a row locked by an active transaction.

3. Orphaned Sessions / Stale Locks: Background application connections or uncommitted batch jobs are holding locks without active processing.

Step-by-Step Solution

Step 1: Identify the Target Object ID

Find the object ID for the table experiencing the ORA-00054 error:

SQL

SELECT object_id, owner, object_name, object_type
FROM dba_objects
WHERE object_name = 'OE_ORDER_LINES_ALL'
  AND owner = 'APPS';

Output:

Plaintext

 OBJECT_ID OWNER  OBJECT_NAME          OBJECT_TYPE
---------- ------ -------------------- -----------
    184920 APPS   OE_ORDER_LINES_ALL   TABLE

Step 2: Identify Blocking Sessions and Held Locks

Execute the following query to correlate V$LOCKED_OBJECT, V$SESSION, and V$SQL to retrieve session details, OS process IDs, and active SQL text:

SQL

SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.osuser,
    s.machine,
    s.program,
    s.status,
    s.module,
    lo.locked_mode,
    l.type AS lock_type,
    p.spid AS os_pid,
    sq.sql_text
FROM v$locked_object lo
JOIN v$session s ON lo.session_id = s.sid
JOIN v$process p ON s.paddr = p.addr
LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
WHERE lo.object_id = 184920;

Output:

Plaintext

       SID    SERIAL# USERNAME   OSUSER   MACHINE     PROGRAM              STATUS   LOCKED_MODE LOCK_TYPE OS_PID SQL_TEXT
---------- ---------- ---------- -------- ----------- -------------------- -------- ----------- --------- ------ ----------------------------------------------
       342      12495 APPS       applmgr  apphost01   JDBC Thin Client     ACTIVE             3 TM        48201  UPDATE OE_ORDER_LINES_ALL SET LINE_STATUS='PENDING' WHERE LINE_ID=92810

Note: LOCKED_MODE = 3 represents a Row Exclusive lock (SX), which prevents exclusive DDL operations on the object.

Step 3: Resolution Options

Option A: Terminate the Blocking Session via SQL (Standard Fix)

If the blocking session is orphaned or safe to stop, terminate it using ALTER SYSTEM KILL SESSION:

SQL

-- Syntax: ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
ALTER SYSTEM KILL SESSION '342,12495' IMMEDIATE;

Output:

Plaintext

System altered.

Option B: Terminate at Operating System Level (RAC / Stuck Session Fix)

If KILL SESSION hangs due to a process stuck in kernel mode, terminate the OS process directly on the target node using the os_pid retrieved in Step 2:

Bash

# On Linux host apphost01
kill -9 48201

For Real Application Clusters (RAC), query GV$SESSION and specify the instance ID:

SQL

SELECT inst_id, sid, serial# 
FROM gv$session 
WHERE sid = 342;

-- Kill on specific instance:
ALTER SYSTEM KILL SESSION '342,12495,@1' IMMEDIATE;

Option C: Non-Destructive Fix Using DDL_LOCK_TIMEOUT

To avoid killing active transactions during migrations or deployments, instruct Oracle to wait for locking transactions to commit/rollback before raising ORA-00054. Set DDL_LOCK_TIMEOUT in seconds for the session:

SQL

-- Instruct the session to wait up to 600 seconds (10 minutes) for locks
ALTER SESSION SET DDL_LOCK_TIMEOUT = 600;

-- Re-execute the DDL command
ALTER TABLE apps.oe_order_lines_all ADD (x_custom_attribute VARCHAR2(50));

Output:

Plaintext

Session altered.

Table altered.

Verification

Run the verification query against V$LOCKED_OBJECT to confirm that all blocking locks on the object have been released:

SQL

SELECT COUNT(*) AS active_locks
FROM v$locked_object
WHERE object_id = 184920;

Output:

Plaintext

ACTIVE_LOCKS
------------
           0

Re-run the original DDL or DML query to verify successful completion:

SQL

SQL> ALTER TABLE apps.oe_order_lines_all ADD (x_custom_attribute VARCHAR2(50));

Table altered.

Prevention & Best Practices

1. Set Session-Level DDL Lock Timeouts for Deployments: Always configure DDL_LOCK_TIMEOUT in deployment scripts and patch drivers to eliminate manual race conditions during online migrations.

2. Application Transaction Control: Ensure application code (Java, PL/SQL) consistently issues COMMIT or ROLLBACK calls. Long-running transactions keeping locks open cause cascading ORA-00054 errors for downstream jobs.

3. Avoid Explicit NOWAIT in Production PL/SQL: Replace SELECT ... FOR UPDATE NOWAIT with bounded wait times using SELECT ... FOR UPDATE WAIT <seconds> to handle brief concurrency spikes gracefully without throwing unhandled exceptions.

4. Oracle EBS Maintenance Windows: Perform DDL patches, table re-organizations, and index builds during designated maintenance windows or after stopping concurrent managers/application services to minimize lock conflicts.