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

Oracle 11g New Feature Parameter DDL_LOCK_TIMEOUT : DDL waiting time for DML Locks



Oracle 11g introduced a new parameter ddl_lock_timeout which controls the waiting time duration for a DDL statement wait for a DML Lock. Prior to 11g and in 11g if you are not specifiying the ddl_lock_timeout, if you perform a ddl operation on a table which is locked by a end user DML, immediately you will get error “ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired”. 
But in Oracle 11g if you specify DDL_LOCK_TIMEOUT, the ddl session will wait till the duration mentioned to release the DML lock on the table. It will not through the immediate error message.

Syntax:

ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 120 ;

ALTER SESSION SET DDL_LOCK_TIMEOUT = 120 ;

The maximum value can be specified is 1,000,000. The default value zero indicates NOWAIT. The values are in seconds.

Example:

Session 1:

22:09:35 SQL> alter system set ddl_lock_timeout=60;

System altered.

Elapsed: 00:00:00.67

22:11:52 SQL> show parameter ddl_lock_timeout

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     60

22:18:04 SQL> update emp1 set age=50;

2 rows updated.

Elapsed: 00:00:00.01

Session 2:

Elapsed: 00:00:00.00
05:05:30 SQL> ALTER INDEX IVC_INPUT_TBL_SRC_CO_NAME_IDX REBUILD PARALLEL;
ALTER INDEX IVC_INPUT_TBL_SRC_CO_NAME_IDX REBUILD PARALLEL
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

                          *

Elapsed: 00:01:01.44
22:19:35 SQL>

In the above example the session 2 waited for one minute to throw the ora-00054 error message because of the DDL_LOCK_TIMEOUT value as 60 sec.