Blog

Learnomate Technologies > Blog > Oracle DBA > ORA-00054: resource busy and acquire with NOWAIT specified

ORA-00054: resource busy and acquire with NOWAIT specified

  • November 10, 2021
  • Posted by: Ankush Thavali
  • Category: Oracle DBA
No Comments

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#';
ORA-00054