Wednesday, June 6, 2012

Oracle 11g: DDL WAIT Option (DDL_LOCK_TIMEOUT)


In Oracle 11g, the new initialization parameter DDL_LOCK_TIMEOUT controls the duration (in seconds) for which a DDL statement will wait for a DML lock.The maximum value of 1,000,000 seconds will result in the DDL statement waiting forever to acquire a DML lock.


The default value of zero indicates a status of NOWAIT. 


SQL> show parameter DDL_LOCK_TIMEOUT


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


If these locks are not available, the commands return with an "ORA-00054: resource busy" error message. 

Follow the below mentioned step to test out the parameter

1. create a table with lets say only one column for the testing
2. insert a test data into it
3. don't commit the changes
4. open a new session.
5. alter the ddl_lock_timeout to any non-zero value (alter session set ddl_lock_timeout= 60);
6. add one more column in the table created in step1
7. session will wait for 60 seconds before failing with ORA-0054: resource busy and acquire.... error
8. go back to first session and commit the changes done in step 2 and repeat the step 6. The changes will be successful


Explicit locking of the table (DML):


The  LOCK TABLE command has a new keyword named as WAIT which lets you specify the maximum time a statement should wait to obtain a DML lock on table.

LOCK TABLE .. IN <lockmode> MODE [ NOWAIT | WAIT n ]

where,

• n is an integer value in seconds
• If you do not specify either WIAT nor NOWAIT, Oracle will wait until the lock is available and acquired

No comments:

Post a Comment