ORA-00054 resource busy and acquire with nowait

The error ORA-00054 occurs if DDL or DML currently running without commits on the target table. The ORA-00054 mostly occurs in sessions that try to change table data or the structure of a table.

Read: ORA-00031: session marked for kill Solution

You can get the information about the locks using the below lists views:

DBA_BLOCKERS: It shows non-waiting sessions holding locks being waited on.
DBA_DDL_LOCKS: It shows information about all DDL locks held or being requested.
DBA_DML_LOCKS: Shows all DML locks held or being requested.
DBA_LOCK_INTERNAL: Displays 1 row for every lock/latch held or requested with the username of who holds the lock.
DBA_LOCKS: Shows all locks/latches held or being requested.
DBA_WAITERS: Shows all sessions waiting on but not holding waited for locks.

To check the lock session details use the below query:

SQL> select a.sid, a.serial#
     from v$session a, v$locked_object b, dba_objects c
     where b.object_id = c.object_id
     and a.sid = b.session_id
     and OBJECT_NAME='EMP';

From the above query you will get the sid and a serial number of the blocking session, use these details and just kill the lock session and unlock the table.

SQL>alter table emp add (cust_id varchar2(3));

After killing the blocking session now you can take any action on the target table and do the modifications.

I hope now you understand how to troubleshoot the Error ORA-00054 resource busy and acquire with nowait. Write your fillings in the comment box. 🙂

#ora 00054

I found this article from tekstream