ORA-00054: Resource Busy and Acquire with NOWAIT Specified or Timeout Expired – Complete Oracle DBA Troubleshooting Guide

The ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired error is one of the most frequently encountered Oracle Database locking errors. It occurs when a session attempts to acquire a lock on a database object that is already locked by another session, and Oracle cannot obtain the required lock immediately.

ORA-00054 commonly occurs during DDL operations such as ALTER TABLE, DROP TABLE, TRUNCATE TABLE, CREATE INDEX, and ALTER INDEX REBUILD. It may also occur during application deployments, Oracle E-Business Suite maintenance, Data Pump operations, schema changes, or any task requiring an exclusive lock while another session is actively using the object.

Unlike deadlocks, ORA-00054 does not indicate a circular dependency between sessions. Instead, it simply means that the requested resource is currently unavailable because another transaction or session holds a conflicting lock.

Resolving ORA-00054 requires identifying the blocking session, understanding the type of lock involved, determining whether the session should be allowed to complete naturally or be terminated, and applying the appropriate corrective action while minimizing disruption to production workloads.

This comprehensive Oracle DBA guide explains Oracle locking architecture, common causes of ORA-00054, production troubleshooting techniques, Oracle RAC, ASM, Oracle Data Guard, Oracle Cloud Infrastructure (OCI) considerations, and best practices for Oracle Database 11g, 12c, 18c, 19c, 21c, and Oracle Database 23ai.

Quick Solution

Identify the session holding the lock, determine whether it is performing legitimate work, wait for the transaction to complete if appropriate, or terminate the blocking session only after evaluating the business impact. Consider using the DDL_LOCK_TIMEOUT parameter instead of NOWAIT for maintenance operations that can safely wait for locks to be released.

Error Message

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

This error indicates that Oracle could not obtain the required lock because another session already holds a conflicting lock on the requested resource.

What is ORA-00054?

ORA-00054 is returned when a session requests a lock that cannot be granted immediately because another session is currently using the same object. If the requesting operation specifies NOWAIT, or if the configured timeout expires before the lock becomes available, Oracle terminates the operation with ORA-00054.

The database itself remains healthy. Only the current SQL statement fails.

The blocking session may be:

Running a transaction
Performing DML operations
Executing DDL
Rebuilding indexes
Importing or exporting data
Waiting for user commit or rollback

Understanding Oracle Locking Architecture

Oracle automatically manages locks to maintain data consistency and transaction isolation. Locks prevent conflicting operations from modifying the same database objects simultaneously.

          Session A

              │

              ▼

      UPDATE EMPLOYEES

              │

      Oracle Acquires Lock

              │

              ▼

          Session B

              │

              ▼

      ALTER TABLE EMPLOYEES

              │

      Lock Available?

          │         │

         Yes        No

          │         │

          ▼         ▼

     Continue    ORA-00054

Oracle releases most transaction locks automatically when the transaction commits or rolls back.

Types of Oracle Locks

1. Row-Level Locks (TX)

Row-level locks protect individual rows during INSERT, UPDATE, and DELETE operations. They allow other sessions to modify different rows in the same table simultaneously.

2. Table Locks (TM)

Table locks protect table structures during DML and DDL operations. DDL operations generally require more restrictive locks than ordinary DML statements.

3. Library Cache Locks

Oracle uses library cache locks to protect SQL statements, PL/SQL objects, and metadata while they are being parsed or executed.

4. DDL Locks

DDL statements such as ALTER TABLE, DROP TABLE, and TRUNCATE TABLE require exclusive access to database objects and often conflict with active DML transactions.

Understanding NOWAIT and DDL_LOCK_TIMEOUT

Many Oracle operations request locks using the NOWAIT option, which instructs Oracle to return an error immediately if the required lock is unavailable.

Alternatively, the DDL_LOCK_TIMEOUT initialization parameter allows DDL statements to wait for a specified number of seconds before raising ORA-00054.

Example:

ALTER SESSION

SET DDL_LOCK_TIMEOUT = 60;

With this setting, Oracle waits up to 60 seconds for the conflicting lock to be released before returning ORA-00054.

Common Causes of ORA-00054

1. Active Transactions

The most common cause is an uncommitted INSERT, UPDATE, or DELETE statement holding locks on the target object.

2. Long-Running SQL Statements

Large batch jobs, reporting queries, or ETL processes may hold locks for extended periods.

3. DDL During Business Hours

Attempting schema changes while applications are actively using database objects frequently results in ORA-00054.

4. Application Sessions Left Open

Applications that do not commit or roll back transactions promptly may retain locks unnecessarily.

5. Oracle Data Pump

Import and export operations can hold metadata locks that temporarily block DDL statements.

6. Index Maintenance

Index rebuilds and maintenance operations often require exclusive locks.

7. Online Application Deployment

Deployments that modify database objects while users remain connected may encounter locking conflicts.

8. Oracle E-Business Suite Maintenance

Online patching and maintenance activities in Oracle E-Business Suite environments may temporarily lock application objects.

Common Symptoms

ALTER TABLE fails.
DROP TABLE fails.
TRUNCATE TABLE returns ORA-00054.
CREATE INDEX or ALTER INDEX REBUILD fails.
Schema deployment scripts stop unexpectedly.
Application upgrade scripts fail.
Maintenance windows exceed expected duration.
DDL statements succeed after users disconnect or transactions commit.

ORA-00054 Compared with Related Oracle Errors

Error	Description	Primary Area
ORA-00054	Requested lock could not be obtained.	Object Locking
ORA-00060	Deadlock detected between sessions.	Deadlocks
ORA-00020	Maximum number of processes exceeded.	Process Limits
ORA-01555	Snapshot too old.	UNDO
ORA-03113	End-of-file on communication channel.	Client/Server Communication

Production DBA Recommendation

Never terminate a blocking session immediately after encountering ORA-00054. First identify the session, determine the business transaction it is performing, and verify whether it is safe to wait or terminate the session. Killing an active production transaction without proper assessment can lead to application failures, user disruption, or incomplete business processes.

Step-by-Step Oracle DBA Troubleshooting

When ORA-00054 occurs, the primary objective is to identify:

Which session is holding the lock?
Which object is locked?
What type of lock is involved?
Is the session active or idle?
Can the transaction safely complete?
Should the blocking session be terminated?

The following production troubleshooting workflow reflects Oracle DBA best practices.

Step 1 – Review the Oracle Alert Log

Although ORA-00054 is generally returned directly to the client session, the Alert Log may contain related DDL failures or application errors.

Typical Alert Log location:

$ORACLE_BASE/diag/rdbms/<db_name>/<instance_name>/trace/

alert_<SID>.log

Look for accompanying errors such as:

ORA-00054
ORA-00060
DDL execution failures
Application deployment logs

Step 2 – Identify the Blocking Session

Oracle provides the DBA_BLOCKERS view to identify sessions currently blocking other sessions.

SELECT *

FROM DBA_BLOCKERS;

If no rows are returned, the lock may have already been released.

Step 3 – Identify Waiting Sessions

Determine which sessions are waiting for the blocking session.

SELECT *

FROM DBA_WAITERS;

This view displays both the waiting session and the session that currently owns the lock.

Step 4 – Review Session Information

Collect detailed information about the blocking session.

SELECT

SID,

SERIAL#,

USERNAME,

STATUS,

PROGRAM,

MACHINE,

SQL_ID,

EVENT

FROM V$SESSION

WHERE SID=<SID>;

Important information includes:

Username
Application name
Current SQL
Waiting event
Session status

Step 5 – Identify Locked Objects

Determine which database object is currently locked.

SELECT

LO.SESSION_ID,

DO.OWNER,

DO.OBJECT_NAME,

DO.OBJECT_TYPE

FROM V$LOCKED_OBJECT LO

JOIN DBA_OBJECTS DO

ON LO.OBJECT_ID=DO.OBJECT_ID;

This immediately identifies the affected table, index, or other database object.

Step 6 – Examine Oracle Locks

The V$LOCK view provides detailed information about Oracle enqueue locks.

SELECT

SID,

TYPE,

ID1,

ID2,

LMODE,

REQUEST,

BLOCK

FROM V$LOCK

ORDER BY SID;

Useful lock types include:

TM – Table locks
TX – Transaction locks
UL – User-defined locks
AE – Edition locks

Step 7 – Identify the SQL Statement

After identifying the blocking session, review the SQL statement currently executing.

SELECT

SQL_ID,

SQL_TEXT

FROM V$SQL

WHERE SQL_ID='<SQL_ID>';

Understanding the SQL often reveals whether the lock is expected or abnormal.

Step 8 – Determine Whether the Session Is Active

Before terminating any session, verify whether it is actively processing a transaction.

SELECT

SID,

STATUS,

LAST_CALL_ET

FROM V$SESSION

WHERE SID=<SID>;

A session waiting for user input may have been left open after an uncommitted transaction.

Step 9 – Wait for Transaction Completion

If the blocking transaction is legitimate and expected to complete shortly, waiting is usually safer than terminating the session.

Many ORA-00054 errors resolve automatically after the blocking transaction commits or rolls back.

Step 10 – Use DDL_LOCK_TIMEOUT

Instead of immediately failing with ORA-00054, Oracle can wait for a configurable period.

ALTER SESSION

SET DDL_LOCK_TIMEOUT=120;

Oracle will wait up to two minutes before returning ORA-00054.

Step 11 – Terminate the Blocking Session (If Necessary)

Terminate the blocking session only after confirming that doing so will not interrupt a critical business transaction.

ALTER SYSTEM

KILL SESSION

'SID,SERIAL#'

IMMEDIATE;

Always coordinate with application owners before killing production sessions.

Step 12 – Oracle RAC Considerations

In Oracle RAC environments, blocking sessions may exist on another cluster node.

Check global sessions using GV$SESSION.
Review GV$LOCK for global locks.
Verify Clusterware health.
Identify cross-instance blocking.

Useful query:

SELECT

INST_ID,

SID,

SERIAL#,

USERNAME

FROM GV$SESSION;

Step 13 – ASM Considerations

ORA-00054 rarely originates from ASM itself, but maintenance involving ASM-managed files may hold metadata locks.

Verify ASM availability.

asmcmd lsdg

Step 14 – Oracle Data Guard Considerations

DDL operations during switchover, failover, or maintenance windows may experience locking conflicts.

Verify role transitions.
Check redo apply status.
Coordinate maintenance across primary and standby databases.

Step 15 – Oracle Cloud Infrastructure (OCI)

For Oracle databases hosted on OCI:

Review Compute Instance utilization.
Monitor long-running application sessions.
Review Autonomous Database maintenance tasks (where applicable).
Check OCI Monitoring dashboards.

Real Production Case Study

During a weekend deployment, a DBA attempted to execute an ALTER TABLE statement on a production application table. Oracle immediately returned ORA-00054. Investigation using DBA_BLOCKERS, V$SESSION, and V$LOCKED_OBJECT revealed that an application server had left an UPDATE transaction open for over two hours.

The DBA contacted the application team, confirmed the transaction was abandoned, terminated the session using ALTER SYSTEM KILL SESSION, and successfully completed the schema modification. The application was then updated to ensure transactions were committed promptly, preventing future locking issues.

Oracle DBA Troubleshooting Checklist

Verification	Status
Alert Log Reviewed	☐
Blocking Session Identified	☐
Waiting Session Identified	☐
Locked Object Identified	☐
SQL Statement Reviewed	☐
Business Impact Assessed	☐
DDL_LOCK_TIMEOUT Considered	☐
Blocking Session Resolved	☐
Post-Change Validation Completed	☐
Root Cause Eliminated	☐

Oracle Version Considerations

ORA-00054 can occur in all supported Oracle Database releases because Oracle's locking mechanism is fundamental to transaction management. Newer Oracle versions provide improved diagnostics, better wait event reporting, and enhanced online DDL capabilities that help DBAs identify and resolve locking conflicts more efficiently.

Oracle Version	Locking & Diagnostic Improvements
Oracle 10g	Automatic Workload Repository (AWR), Active Session History (ASH), improved locking diagnostics.
Oracle 11g	Automatic Diagnostic Repository (ADR), enhanced online index operations.
Oracle 12c	Online table move, Multitenant architecture, improved online DDL support.
Oracle 18c / 19c	Better online maintenance capabilities, optimizer enhancements, improved wait event analysis.
Oracle 21c / 23ai	Enhanced observability, Autonomous Health Framework (AHF), OCI monitoring integration.

Oracle Lock Management Best Practices

Keep transactions as short as possible.
Commit or roll back transactions promptly.
Avoid performing DDL during peak business hours.
Schedule maintenance windows for schema changes.
Use DDL_LOCK_TIMEOUT instead of NOWAIT when appropriate.
Monitor blocking sessions proactively.
Review long-running transactions regularly.
Use online DDL features where supported.
Coordinate application deployments with database maintenance.
Educate developers on proper transaction management.

Common Mistakes

Killing production sessions without investigating the business impact.
Ignoring uncommitted transactions.
Running DDL during peak application usage.
Leaving application transactions open unnecessarily.
Ignoring blocking session alerts.
Using NOWAIT unnecessarily in maintenance scripts.
Not configuring DDL_LOCK_TIMEOUT where appropriate.
Assuming ORA-00054 indicates database corruption.
Ignoring RAC-wide blocking sessions.
Failing to verify successful completion after resolving the lock.

Useful SQL Queries

Identify Blocking Sessions

SELECT *

FROM DBA_BLOCKERS;

Identify Waiting Sessions

SELECT *

FROM DBA_WAITERS;

View Locked Objects

SELECT

LO.SESSION_ID,

DO.OWNER,

DO.OBJECT_NAME,

DO.OBJECT_TYPE

FROM V$LOCKED_OBJECT LO

JOIN DBA_OBJECTS DO

ON LO.OBJECT_ID = DO.OBJECT_ID;

Review Active Locks

SELECT

SID,

TYPE,

LMODE,

REQUEST,

BLOCK

FROM V$LOCK;

Review DDL Lock Timeout

SHOW PARAMETER DDL_LOCK_TIMEOUT;

Useful Linux Commands

Command	Purpose
`top`	Monitor CPU and active Oracle processes.
`ps -ef \| grep ora_`	List Oracle background processes.
`vmstat 5`	Monitor CPU, memory, and process activity.
`iostat -x 5`	Monitor storage performance.
`tail -100 alert.log`	Review recent Oracle Alert Log entries.
`srvctl status database`	Check Oracle RAC database status.

ORA-00054 Troubleshooting Flowchart

ORA-00054

      │

      ▼

Identify Blocking Session

      │

      ▼

Identify Locked Object

      │

      ▼

Review SQL Statement

      │

      ▼

Is Transaction Active?

      │

 ┌────┴─────┐

 │          │

Yes         No

 │          │

 ▼          ▼

Wait     Verify Session

 │          │

 ▼          ▼

Completed?   Kill Session?

 │          │

 ├──Yes─────┘

 ▼

Retry DDL

 │

 ▼

Validate Success

 │

 ▼

Monitor for Recurrence

Frequently Asked Questions (FAQ)

Is ORA-00054 the same as a deadlock?

No. ORA-00054 indicates that a required lock is currently unavailable. ORA-00060 indicates a true deadlock where two or more sessions are waiting on each other in a circular dependency.

Should I always kill the blocking session?

No. Always determine whether the session is performing a legitimate business transaction. In many cases, waiting for the transaction to complete is the safest approach.

How can I avoid ORA-00054 during maintenance?

Schedule maintenance during periods of low database activity, use DDL_LOCK_TIMEOUT, ensure application transactions are committed promptly, and coordinate schema changes with application teams.

Can Oracle RAC cause ORA-00054?

Yes. In Oracle RAC environments, the blocking session may reside on another instance. Use GV$SESSION and GV$LOCK to investigate cluster-wide locking.

Does restarting the database fix ORA-00054?

Restarting the database releases all locks, but it should never be used as a routine solution for ORA-00054. The correct approach is to identify and resolve the blocking transaction while minimizing disruption to users.

About the Author

Rana Abdul Wahid is an Oracle Database Consultant with over 15 years of experience in Oracle Database Administration, Oracle RAC, Oracle Data Guard, RMAN Backup & Recovery, Oracle E-Business Suite, Oracle Cloud Infrastructure (OCI), Performance Tuning, Linux/Unix Administration, MySQL, Microsoft SQL Server, PostgreSQL, and enterprise database management.

He shares production-tested Oracle DBA solutions, performance tuning techniques, backup and recovery strategies, and enterprise troubleshooting guides to help database professionals solve complex Oracle issues efficiently.

Learn more about the author →

Conclusion

The ORA-00054: Resource Busy and Acquire with NOWAIT Specified or Timeout Expired error indicates that Oracle could not obtain the required lock because another session currently holds a conflicting lock. In most cases, the database is operating normally—the challenge is identifying and resolving the blocking transaction safely.

A structured troubleshooting approach—reviewing blocking and waiting sessions, identifying locked objects, analyzing SQL statements, evaluating business impact, and using DDL_LOCK_TIMEOUT where appropriate—enables DBAs to resolve locking conflicts with minimal disruption to production systems.

By keeping transactions short, scheduling DDL during maintenance windows, monitoring long-running sessions, and following Oracle locking best practices, organizations can significantly reduce ORA-00054 occurrences and maintain reliable database availability.

Final DBA Recommendation

Treat ORA-00054 as a transaction management issue rather than simply a locking error. Always identify the blocking session, understand the business process behind it, and choose the least disruptive resolution. A well-designed application that commits transactions promptly and properly schedules maintenance activities is the most effective long-term defense against ORA-00054.

Found this guide helpful? Explore our Oracle Error Codes Guide for more production-tested Oracle DBA troubleshooting guides, performance tuning techniques, and backup & recovery solutions.

Oracle Database Error Solutions & DBA Knowledge Base