Oracle 11g R1: DDL_LOCK_TIMEOUT

Whenever you are trying to alter a table to add a new column, exclusive lock will be acquired on the table, if the table is used by any other transaction at the same time oracle will throw the following error.

SQL> DESC T

Name            Null?    Type
--------------- -------- -------------
OBJECT_ID       NOT NULL NUMBER
OBJECT_NAME     NOT NULL VARCHAR2(30)

SQL> ALTER TABLE T ADD STATUS CHAR(1);

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.

Note: I have opened one more Session #2 and inserted one record (DML) to table t and switched to Session #1 without issuing COMMIT in Session #2

The table 'T' is being used right now by another transaction by someother session, so getting an exclusive lock on the table 'T' may be not impossible at this moment.

Once the user transaction completes, then the user saves tranactions by commit statement, then the acquired lock will be released. Then we can issue the alter command to add a new column.

During busy system time 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.

In Oracle Database 11g, we have a solution for the above problem: DDL Wait option

SQL> ALTER SESSION SET DDL_LOCK_TIMEOUT = 30;

Session altered.

Now, If a DDL statement in the session does not get the exclusive lock, it will NOT throw an error. Instead, it will wait for 30 seconds. During this period, it continously re-tries the DDL operation until it's successful or the time expires, whichever comes first.

SQL> ALTER TABLE T ADD STATUS CHAR(1);

The above statement hangs and does not give any error, instead of trying repeatedly for an exclusive lock to exeucte alter command.
Globally (for all sessions) also you can enable this feature by the below command;

SQL> ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 30;

System altered.

Don't have Oracle 11g R1?, dont worry, still we can achieve this; Here is my PLSQL code for the same functionality, it will work in any verion of oracle

SQL> set serveroutput on
SQL> declare
  2  v_start   date;
  3  v_end     date:= sysdate+(30+1)/(24*60*60); -- adding one second additionally as an overhead
  4  is_locked boolean:=true;
  5  begin
  6    while (true)
  7     loop
  8       begin
  9         execute immediate 'alter table t add column status char(1)';
 10         dbms_output.put_line('Exclusive lock on table T acquired and column has been added successfully.');
 11         exit;
 12       exception
 13         when others then
 14           null;
 15       end;
 16       v_start := sysdate;  -- Assigning current time
 17       if (v_start = v_end) then  -- Checking for exit criteria
 18         is_locked:=false;
 19         exit;  -- breaking the loop
 20       end if;
 21     end loop;
 22     if (is_locked = false) then
 23       dbms_output.put_line('Sorry, Exclusive Lock request on table T has been failed. Please try again...');
 24     end if;
 25  end;
 26  /
Sorry, Exclusive Lock request on table T has been failed. Please try again...

PL/SQL procedure successfully completed.

Cheers,
Hope you have enjoyed the experiment.
Post your comments (if any).