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:
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.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google