Select Page

ORA-00054: resource busy and acquire with NOWAIT specified

Jeremiah Wilton | | September 22, 2009

When trying to perform DDL such as truncate, drop or alter on Oracle objects that are in use by other users, you may encounter the error ORA-00054: resource busy and acquire with NOWAIT specified. This 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]

A new feature in Oracle 11g allows you to overcome this problem. 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]

Older solutions – 10g and before:

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. We 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

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.

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Imagine there are over one hundred logins in the source server and you need to migrate them all over to the destination server. Wouldn’t it be awesome if we could automate the process by generating the scripts for the required tasks?

JP Chen | October 1, 2015

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS