DDL_LOCK_TIMEOUT的作用

来源：互联网发布：5g云网络wlxiu 编辑：程序博客网时间：2024/06/07 02:46

11g的DDL_LOCK_TIMEOUT参数的作用，从字面上看，就是DDL获取锁的超时时间。

先看下官方文档的介绍：

DDL_LOCK_TIMEOUT

PropertyDescriptionParameter typeIntegerDefault value0ModifiableALTER SESSIONRange of values0 to 1,000,000 (in seconds)BasicNo

DDL_LOCK_TIMEOUT specifies a time limit for how long DDL statements will wait in a DML lock queue. The default value of zero indicates a status of NOWAIT. The maximum value of 1,000,000 seconds will result in the DDL statement waiting forever to acquire a DML lock.
If a lock is not acquired before the timeout period expires, then an error is returned.

从解释看，很好理解，该参数表示了执行DDL语句时需要等待DML锁的超时时间，默认0表示当无法立即获得DML锁时，会立即抛出NOWAIT，并且支持session级修改。

实验：

SQL> show parameter ddl_lock_timeout

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout integer0

SQL> desc t;

Name Null? Type
----------------------------------------- -------- ----------------------------
TID NOT NULL NUMBER(38)
CLS_NAME NOT NULL VARCHAR2(30)

session1：

SQL> update t set cls_name='a' where tid=1;

0 rows updated.

session2：

SQL> drop table t;

drop table t
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Elapsed: 00:00:00.00

session2：

SQL> alter session set ddl_lock_timeout = 10;

Session altered.

SQL> drop table t;
drop table t
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Elapsed: 00:00:10.01

0 0