ORA-00054: Resource Busy and Acquire With NOWAIT Specified

Need an ORA-00054 fix? Follow the steps below to solve an ORA-00054 truncate error.

When trying to perform DDL such as truncate, drop, or alter Oracle objects that are in use by other users, you may encounter the database error ORA-00054: resource busy and acquire with NOWAIT specified.

This ORA-00054 error occurs even if you acquire an exclusive DML lock on the table using lock table. Locking a table does not guarantee the success of any subsequent DDL statement on a table or associated indexes. DDL statements must obtain the library cache lock in order to perform DDL, and until recently, there has been no manual mechanism to guarantee possession of a library cache lock.

[code language=”sql”]SQL> alter table foo add (bar varchar2(10));
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified[/code]

Oracle 11g – ORA-00054 Fix

A new feature in Oracle 11g allows you to overcome this ORA-00054 nowait error. If you would like your DDL to wait for the object to become available, simply specify how long you would like it to wait:

[code language=”sql”]SQL> alter session set ddl_lock_timeout = 600;
Session altered.
SQL> alter table foo add (bar varchar2(10));
Table altered.[/code]

Oracle 10g and Earlier Versions – ORA-00054 Fix

If you don’t have an Oracle version with ddl_lock_timeout, you can still devise a way to get that DDL to run on an active segment. You can use a PL/SQL procedure that loops trying to execute DDL, stopping only when it finally succeeds. Jonathan Lewis wrote a simple stored procedure to do this, and it is included here with his permission:

[code language=”sql”]create or replace procedure do_ddl(m_sql varchar2) as
in_use exception;
pragma exception_init(in_use, -54);
begin
while true loop
begin
execute immediate m_sql;
exit;
exception
when in_use then null;
end;
dbms_lock.sleep(0.01);
end loop;
end;[/code]

Online Redefinition in Oracle 9i And Above

Online redefinition is also possible in Oracle 9i and above using the dbms_redefinition package. This package creates interim objects to take DML and store data while the original table is being redefined. It is useful for a variety of activities that cannot be accomplished with a single DDL statement, such as moving or reorganizing a segment. For most DDL, the PL/SQL procedure above is sufficient and avoids the unnecessary complexity of dbms_redefinition.

Datavail’s Oracle Consultants

If you need help with the ORA-00054 error code, or any other Oracle truncate errors, contact us at (866) 815-9675. At Datavail, we’re an Oracle Platinum Partner. Our team of 1,000 database professionals monitors and manages more than 300,000 applications.