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

Home » Articles » 11g » Here

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

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.