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_TIMEOU T which can be set using ALTER SESSION and ALTER SYSTEM commands.
Eg:
[code=oracle]
-- 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_TIMEOU T to 45 Secs
SQL> ALTER SESSION SET DDL_LOCK_TIMEOU T = 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
[/code]
In case if the COMMIT is executed in Session 1, within 45 Seconds, then the ALTER TABLE COMMAND completes Successfully
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_TIMEOU T which can be set using ALTER SESSION and ALTER SYSTEM commands.
Eg:
[code=oracle]
-- 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_TIMEOU T to 45 Secs
SQL> ALTER SESSION SET DDL_LOCK_TIMEOU T = 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
[/code]
In case if the COMMIT is executed in Session 1, within 45 Seconds, then the ALTER TABLE COMMAND completes Successfully