簡單的記錄了一個測試過程。

[@more@]

11gR1之前ddl是不會等待的，如果有不相容的鎖，直接返回錯誤：ORA-00054: resource busy and acquire with NOWAIT specified，從11gR1開始，返回的錯誤有一點小小的變化：

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

錯誤提示當中多了or timeout expired，這個提示其實就是針對DDL可等待這個新特性的。

11gR1引入了一個引數：

DDL_LOCK_TIMEOUT

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.

下面是一個簡單的測試過程：

session 1：

SQL> update t set id=1 where name='a';

1 row updated.

SQL>

session 2：

SQL> show parameter ddl

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout integer 0
enable_ddl_logging boolean FALSE
SQL> drop table t;
drop table t
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL> alter session set ddl_lock_timeout=10;

Session altered.

SQL> set time on
11:49:38 SQL>
11:49:44 SQL> drop table t;
drop table t
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

11:49:55 SQL>
這裡我們能感覺到延遲了10秒之後才報錯。