Oracle 11g Administrator’s Guide笔记1：DDL_LOCK_TIMEOUT初始化参数介绍

今天在看Oracle 11gR2 Administrator‘s Guide(文档编号E25494-01)时，在第102页读到一个新的初始化参数：DDL_LOCK_TIMEOUT，该参数是从11g Release 1开始已经引入的，用来控制执行DDL语句时需要获取独占锁(exclusive locks)的延时时间，如果在该参数指定的时间范围内获取不到独占锁的话，那么DDL语句就会失败。

DDL_LOCK_TIMEOUT参数的取值范围是0~1000000秒，是一个整形参数，既可以在会话级别(ALTER SESSION)也可以在系统级别(ALTER SYSTEM)动态调整。其默认值是0，也就是说，执行DDL语句是要求立即获取独占锁资源，如果无法获取，那么所执行的DDL语句将立即报错。

我们也知道，执行DML语句时需要获取2个锁资源：

① 针对正在更新的一行或者多行的一个行级独占锁EXCLUSIVE，防止在事务结束之前有其它会话修改了被锁定行的数据，也叫排他锁；
② 针对要更新的表的 ROW EXCLUSIVE 表级锁定。这可避免在进行更改时由另一会话锁定整个表(可能会删除、截断该表，或修改了该表的表结构)，也叫共享锁；

正是由于锁的特征，结合DDL_LOCK_TIMEOUT初始化参数，实验如下：

会话1，创建一张表，并在该表上执行DML事务，且不提交，使该DML事务获取上述的两个锁资源：

SQL> conn / as sysdba;
Connected.
SQL> select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> show parameter ddl_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     FALSE
SQL> conn hr/hR;
Connected.
SQL> create table t(id number);

Table created.

SQL> insert into t values(1);

1 row created.

SQL> select * from t;

        ID
----------
         1

SQL>

会话2，会话级别修改DDL_LOCK_TIMEOUT初始化参数超出0~1000000范围值，直接报错：

SQL> conn hr/hR;
Connected.
SQL> alter session set ddl_lock_timeout=1000001;
ERROR:
ORA-00068: invalid value 1000001 for parameter ddl_lock_timeout, must be
between 0 and 1000000


SQL>

会话2，执行DDL语句，使之需要获取独占锁，立即抛出错误，因为DDL_LOCK_TIMEOUT初始化参数默认值是0：

SQL> alter table t add  name varchar2(20) default 'www.OracleOnLinux.cn';
alter table t add  name varchar2(20) default 'www.OracleOnLinux.cn'
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


SQL>

会话2，调整DDL_LOCK_TIMEOUT初始化参数为30秒，在30秒内获取不到独占锁，最终抛出错误：

SQL> alter session set ddl_lock_timeout=30;

Session altered.

SQL> set time on; 
17:00:26 SQL> alter table t add  name varchar2(20) default 'www.OracleOnLinux.cn';
alter table t add  name varchar2(20) default 'www.OracleOnLinux.cn'
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


17:00:58 SQL>

会话2重新执行DDL，当会话1提交事务后，会话2在30秒内获取到独占锁，DDL执行成功：

17:03:13 SQL> alter table t add  name varchar2(20) default 'www.OracleOnLinux.cn';

Table altered.

17:03:21 SQL> select * from t;

        ID NAME
---------- --------------------
         1 www.OracleOnLinux.cn

17:03:32 SQL>

Oracle 11gR2 Administrator's Guide，仍然需要多读多看，多做实验。