DDL_LOCK_TIMEOUT : DDL With the WAIT Option

DDL commands require exclusive locks on internal structures. If these locks are not available some commands return with an "ORA-00054: resource busy" error message, which can be especially frustrating when trying to modify objects that are accessed frequently. To get round this Oracle 11g includes the DDL_LOCK_TIMEOUT parameter, which can be set at instance or session level using the ALTER SYSTEM and ALTER SESSION commands respectively.

The DDL_LOCK_TIMEOUT parameter indicates the number of seconds a DDL command should wait for the locks to become available before throwing the resource busy error message. The default value is zero. Create a new table and insert a row, but don't commit the insert.

create table lock_tab ( id number insert into lock_tab values (1);

Leave this session alone and in a new session modify the column. The "ORA-00054" error is returned immediately.

alter table lock_tab modify ( id number not null alter table lock_tab modify ( ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Set the DDL_LOCK_TIMEOUT at session level to a non-zero value and attempt to modify the column again. The result is the same, but it takes 30 seconds before the error is returned.

alter session set ddl_lock_timeout=30; alter table lock_tab modify ( id number not null alter table lock_tab modify ( ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

If we repeat the ALTER TABLE command and commit the insert in the first session within 30 seconds, the ALTER TABLE will return a successful message.

alter table lock_tab modify ( id number not null Table altered.

For more information see:

DDL_LOCK_TIMEOUT

DDL_LOCK_TIMEOUT : DDL With the WAIT Option