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.
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. 🙂
I found this article from tekstream