Blog

ORA-00054: resource busy and acquire with NOWAIT specified

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:

<pre>SQL&gt; alter table emp read only;
Session altered.</pre>
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#'; 

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.