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.
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.
Comments
Post a Comment