ORA-00054: resource busy and acquire with NOWAIT specified

SQL> alter table emp add (address varchar2(100 char));

*

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified

 

Solution in 11g:

you can set ddl_lock_timeout in Oracle 11g which allow DDL to wait for the object to become available. You need to specify how long you would like it to wait. Range of values is from 0 to 1,000,000 (in seconds). Default value is 0 which indicates a status of NOWAIT. Maximum value is 1,000,000 which indicate the DDL statement will wait forever. 


SYS@ipxtest 04:58:01> show parameter ddl_lock_
NAME                       TYPE                              VALUE
--------- --------------------------------- ------------------------------
ddl_lock_timeout                     integer                           0
 
Here specified 20 seconds to wait:
SQL> alter session set ddl_lock_timeout = 20;
Session altered.
SQL> alter table emp add (address varchar2(100 char));
Table altered. 

If a lock is not obtained before the timeout period expires, then ORA-00054 will return. 

Comments