askMLabs

DDL Wait Option :

Any DDL operations on a table requires an exclusive lock on the table. Oracle tries to get this exclusive lock if we issue any DDL command.
If these locks are not available the commands return with an "ORA-00054: resource busy" error message. This can be especially frustrating when trying to modify objects that are accessed frequently.
In a typical business environment, the window for locking the table exclusively does open periodically, but the DBA may not be able to perform the alter command exactly at that time.

So one cant keep on trying the same command over and over again until he gets an exclusive lock.

In oracle 10g we didn't have any other alternatives. So we had to wait until resource is free and in fact in production database you might need hours to complete your DDL jobs and you might need to try it frequently to test when resource become free.

In Oracle Database 11g, We have a better option: the DDL Wait option.

Set ddl_lock_timeout init parameter either at session level or system level.

The parameter DDL_LOCK_TIMEOUT specifies a time limit for how long DDL statements will wait in a DML lock queue.
- The default value is zero which indicates a status of NOWAIT.
- The maximum value is 1,000,000 seconds which indicates the DDL statement will wait forever to acquire a DML lock.
- If you specify time in the DDL_LOCK_TIMEOUT parameter and if a lock is not acquired before the timeout period expires, then an error is returned.

===========================================================
DEMO    DEMO      DEMO       DEMO         DEMO         DEMO         DEMO            DEMO
============================================================

S1-SQL> CREATE TABLE TEST ( col1  NUMBER);

S1-SQL> INSERT INTO TEST VALUES (1); -- Table gets exclusive lock in session1

S2-SQL> show parameter ddl_lock_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0

S2-SQL> Drop table TEST ;

drop table TEST
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

-- It immediately fails without any wait

S2-SQL> alter session set ddl_lock_timeout = 20;

S2-SQL> Drop table TEST;

Drop table TEST
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

-- It fails after waiting for 20 seconds

Solution
========
Issue the DDL command in Session-2 and commit the transactions in Session-1 before 20 sec ( ddl_lock_timeout parameter value ).

S2-SQL> Drop table TEST;

S1-SQL> commit; -- releases the exclusive lock on table TEST in session1

S2-SQL> You will see that the table is altered without error in session2

S2-SQL> Drop table TEST;

Table dropped.