ORA-00054: Resource Busy and Acquire With NOWAIT Specified
Author: Jeremiah Wilton | | September 22, 2009
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.
Related Posts
How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified
The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.
Data Types: The Importance of Choosing the Correct Data Type
Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.
How to Recover a Table from an Oracle 12c RMAN Backup
Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.