DDL_LOCK_TIMEOUT in Oracle 11g

[amitpatel66]

DDL Commands require exclusive lock before performing any action on the database objects. If the locks are not available then the command will return "ORA-00054: resource busy" error.

In order the control the TIMEOUT for the DDL Command to wait for acquiring the LOCK on the object on which the DDL command is performed, use the parameter DDL_LOCK_TIMEOUT which can be set using ALTER SESSION and ALTER SYSTEM commands.

Eg:

 
-- Session 1
 
SQL> CREATE TABLE test1(id NUMBER);
 
SQL> INSERT INTO test1 VALUES(1);
 
--Dont Commit in Session 1
 
-- Open Session 2
--Set the DDL_LOCK_TIMEOUT to 45 Secs
 
SQL> ALTER SESSION SET DDL_LOCK_TIMEOUT = 45;
 
SQL> ALTER TABLE test1 ADD(desc VARCHAR2(100));
 
-- The Session 2 waits for 45 Seconds and then trhows the below error:
 
ALTER TABLE test1 ADD (
            *
ERROR at line 1:
 
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
 
 

In case if the COMMIT is executed in Session 1, within 45 Seconds, then the ALTER TABLE COMMAND completes Successfully