ORA-00054: resource busy and acquire with NOWAIT specified Posted by MOHAMMED ABDUL HAMEED on 6:11 AM

SQL> alter table Details add (mobile varchar2(15));
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Workaround :

1 . In Oracle 11g you can set ddl_lock_timeout i.e. allow DDL to wait 
for the object to become available, simply specify how long you would like it to wait:

SQL> alter session set ddl_lock_timeout = 500;
Session altered.

SQL> alter table Details add (mobile varchar2(15));
Table altered.

2.  We can mark your table as read-only to prevent DML:
SQL> alter table emp read only;
Session altered.

SQL> alter table Details add (mobile varchar2(15));
Table altered. 

3. This workaround is not preferred on production database , can you use according to the criticality of the problem. 

Identify the session which is locking the object

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='Details';

kill that session using

SQL> alter system kill session 'sid,serial#';