DDL With the WAIT Option (DDL_LOCK_TIMEOUT) in Oracle Database 11g Release 1-优快云博客

本文介绍在Oracle Database 11g Release 1中如何利用DDL_LOCK_TIMEOUT参数来解决DDL命令因资源繁忙导致的ORA-00054错误。通过设置该参数，可以在指定时间内等待锁资源释放，从而避免命令失败。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

http://www.oracle-base.com/articles/11g/ddl-lock-timeout-11gr1.php

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.

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

DDL_LOCK_TIMEOUT