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;
SQL> alter table foo add (bar varchar2(10));
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
pragma exception_init(in_use, -54);
while true loop
execute immediate m_sql;
when in_use then null;
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.
The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.