DDL With the WAIT Option (DDL_LOCK_TIMEOUT) in Oracle Database 11g Release 1
DDL commands require exclusive locks on internal structures. If these locks are not available the commands return with an "ORA-00054: resource busy" error message. This 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. To see it in action, 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, set the
DDL_LOCK_TIMEOUT at session level to a non-zero value and attempt to add a column to the table.
ALTER SESSION SET ddl_lock_timeout=30; ALTER TABLE lock_tab ADD ( description VARCHAR2(50) );
The session will wait for 30 seconds before failing.
ALTER TABLE lock_tab ADD ( * 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 ADD ( description VARCHAR2(50) ); Table altered. SQL>
For more information see:
Hope this helps. Regards Tim...