DDL With the WAIT Option (DDL_LOCK_TIMEOUT)

最新推荐文章于 2024-09-25 19:56:40 发布

转载最新推荐文章于 2024-09-25 19:56:40 发布 · 1.1k 阅读

文章标签：

#oracle 11g

Oracle 11g 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了Oracle 11g中解决DDL命令因资源繁忙导致错误的问题。通过设置DDL_LOCK_TIMEOUT参数，可以指定DDL命令等待锁的时间，有效避免了频繁访问对象时出现的ORA-00054: resource busy错误。

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

Hope this helps.