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.
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 SQL>
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 SQL>
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. SQL>
For more information see:
Hope this helps. Regards Tim...