Translate

Search This Blog

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

DDL Wait Option in 11g:

Any DDL operation on table/index in a session can not succeed if table is in used by some transaction(DML,acuired TM lock) by other session

SQL> alter table trans add (tran_code varchar2(10));
But instead of getting  “Table altered”, DBA gets:
alter table trans add (tran_code varchar2(10));
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

The error message says the table is being used right now, probably by a transaction(DML), so getting an exclusive lock on the table may be next to impossible. Of course, the rows of the table are not locked forever. When sessions perform commit the locks on those rows are released, but before that unlock period gets very far, other sessions may update some other rows of the table—and thus the slice of time to get the exclusive lock on the table vanishes.

In Oracle Database 11g DBA has a better option: the DDL Wait option. DBA issues:
SQL> alter session set ddl_lock_timeout = 10;
Session altered.

Now, when a DDL statement in the session does not get the exclusive lock, it will not error out. Instead, it will wait for 10 seconds. In that 10 seconds, it continually re-tries the DDL operation until it’s successful or the time expires, whichever comes first. When DBA issues:
SQL>  alter table trans add (tran_code varchar2(10));
the statement hangs and does not error out.  This gets completed as soon as TM lock is released by DDL. If you issue ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 10 it can be set at the system level as well.

Follow by Email