ORA-00054: resource busy and acquire with NOWAIT specified
Cause: The NOWAIT keyword forced a return to the command prompt
because a resource was unavailable for a LOCK TABLE or SELECT FOR
UPDATE command.
Action: Try the command after a few minutes or enter the command without
the NOWAIT keyword.
Example:
SQL> alter table emp add (mobile varchar2(15));
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
How to avoid the ORA-00054:
- Execute DDL at off-peak hours, when database is idle.
- Execute DDL in maintenance window.
- Find and Kill the session that is preventing the exclusive lock.
Other Solutions:
Solution 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 = 600;
Session altered.
SQL> alter table emp add (mobile varchar2(15));
Table altered.
Solution 2:
Also In 11g, you can mark your table as read-only to prevent DML:
SQL> alter table emp read only;
Session altered.
SQL> alter table emp add (mobile varchar2(15));
Table altered.
Solution 3 (for 10g):
DECLARE
MYSQL VARCHAR2(250) := 'alter table emp add (mobile varchar2(15))';
IN_USE_EXCEPTION EXCEPTION;
PRAGMA EXCEPTION_INIT(IN_USE_EXCEPTION, -54);
BEGIN
WHILE TRUE LOOP
BEGIN
EXECUTE IMMEDIATE MYSQL;
EXIT;
EXCEPTION
WHEN IN_USE_EXCEPTION THEN
NULL;
END;
DBMS_LOCK.SLEEP(1);
END LOOP;
END;
Solution 4:
Step 1: Identify the session which is locking the object
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';
Step 2: kill that session using
alter system kill session 'sid,serial#';
Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14219/e0.htm
