DDL With the WAIT Option (DDL_LOCK_TIMEOUT)
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...