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

advertisements

_____________________________________________________________________________________________________________________

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:

SQL> set timing on

SQL> set time on

22:17:52 SQL> create index emp1_idx1 on emp1(name);

create index emp1_idx1 on emp1(name)

                          *

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.

_____________________________________________________________________________________________________________________

Website Stats