DDL_LOCK_TIMEOUT的作用，ddl_lock_timeout

DDL_LOCK_TIMEOUT的作用，ddl_lock_timeout

11g的DDL_LOCK_TIMEOUT參數的作用，從字面上看，就是DDL擷取鎖的逾時時間。

先看下官方文檔的介紹：

DDL_LOCK_TIMEOUT

Property	Description
Parameter type	Integer
Default value	0
Modifiable	ALTER SESSION
Range of values	0 to 1,000,000 (in seconds)
Basic	No

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