简单的记录了一个测试过程。

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秒之后才报错。