8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 11g » Here

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.

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

SQL>

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

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...

Back to the Top.