Oracle11g新特性：在线操作功能增强-可等待DDL操作

11g中在线处理功能得到了很大增强，其中包括在线修改表结构，在线创建或重建索引，建立不可见索引，表增加非空字段，在线DDL以及对象依赖性细化等。

    在11g以前，DDL操作都要获取对象的排他锁，如果当时无法获取，则会马上报错：

SQL> conn yangtk/yangtk@ytk102已连接。
SQL> create table t (id number);
表已创建。
SQL> insert into t values (1);
已创建 1 行。

    在当前会话不提交，保持对t表的锁。然后在另外的session登陆，尝试对t表进行ddl操作： 

  
  

   
   
   
   SQL
   
   >
   
    conn yangtk
   
   /
   
   yangtk
   
   @ytk102已连接
   
   。
SQL
   
   >
   
    
   
   set
   
    sqlp 
   
   '
   
   SQL2> 
   
   '
   
   
SQL2
   
   >
   
    
   
   truncate
   
    
   
   table
   
    t;

   
   truncate
   
    
   
   table
   
    t

   
   *
   
   第 
   
   1
   
    行出现错误:
ORA
   
   -
   
   00054
   
   : 资源正忙, 但指定以 NOWAIT 方式获取资源
SQL2
   
   >
   
    
   
   drop
   
    
   
   table
   
    t;

   
   drop
   
    
   
   table
   
    t

   
   *
   
   第 
   
   1
   
    行出现错误:
ORA
   
   -
   
   00054
   
   : 资源正忙, 但指定以 NOWAIT 方式获取资源
SQL2
   
   >
   
    
   
   alter
   
    
   
   table
   
    t 
   
   add
   
    name 
   
   varchar2
   
   (
   
   10
   
   );

   
   alter
   
    
   
   table
   
    t 
   
   add
   
    name 
   
   varchar2
   
   (
   
   10
   
   )

   
   *
   
   第 
   
   1
   
    行出现错误:
ORA
   
   -
   
   00054
   
   : 资源正忙, 但指定以 NOWAIT 方式获取资源
SQL2
   
   >
   
    
   
   select
   
    
   
   *
   
    
   
   from
   
    v$version;
BANNER

   
   --
   
   -------------------------------------------------------------- 
   
   

   
   Oracle 
   
   Database
   
    10g Enterprise Edition Release 
   
   10.2
   
   .
   
   0.1
   
   .
   
   0
   
    
   
   -
   
    Prod
PL
   
   /
   
   SQL Release 
   
   10.2
   
   .
   
   0.1
   
   .
   
   0
   
    
   
   -
   
    Production
CORE 
   
   10.2
   
   .
   
   0.1
   
   .
   
   0
   
    Production
TNS 
   
   for
   
    
   
   32
   
   -
   
   bit
   
    Windows: Version 
   
   10.2
   
   .
   
   0.1
   
   .
   
   0
   
    
   
   -
   
    Production
NLSRTL Version 
   
   10.2
   
   .
   
   0.1
   
   .
   
   0
   
    
   
   -
   
    Production 
  
  

    这就是11g以前的DDL处理方式，在11g中，用户可以为DDL设置一个等待时间，这样DDL在获取不到排他锁时，会根据设置等待相应的时间，如果到时仍然获取不到锁，那么才会报错。

    登陆11g进行同样的处理：
SQL> conn yangtk/yangtk@ora11g已连接。
SQL> create table t (id number);
表已创建。
SQL> insert into t values (1);
已创建 1 行。

    登陆第二个会话，发出DDL操作：

SQL2> conn yangtk/yangtk@ora11g已连接。
SQL2> set timing on
SQL2> truncate table t;
truncate table t
*第 1 行出现错误:
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效
已用时间: 00: 00: 00.07

    DDL仍然马上报错，这是由于控制DDL等待时间的初始化参数DDL_LOCK_TIMEOUT默认值是0，下面修改这个默认值：

SQL2> alter session set ddl_lock_timeout = 5;
会话已更改。
已用时间: 00: 00: 00.06
SQL2> truncate table t;
truncate table t
*第 1 行出现错误:
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效
已用时间: 00: 00: 05.07

    如果在等待时间内第一个会话进行提交，那么DDL就可以获取到排他锁，从而开始操作。

SQL2> alter session set ddl_lock_timeout = 60;
会话已更改。
已用时间: 00: 00: 00.04
SQL2> truncate table t;
返回会话1进行提交：
SQL> commit;
提交完成。

    会话2在会话1提交后，得到T表的锁，开始TRUNCATE操作：

表被截断。
已用时间: 00: 00: 13.03
SQL2> select * from v$version;
BANNER
--------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
已用时间: 00: 00: 00.21

    注意，将DDL_LOCK_TIMEOUT参数设置过大也可能造成潜在的问题，虽然DDL还没有获取到排他锁，不能进行操作，但是它已经在T上增加了锁信息，这会导致后续的DML都会被锁住，直到DDL等待超时或锁住DDL的会话提交或回滚使得DDL顺利完成。

    这对于繁忙的系统可能造成很严重的问题，设置之前需要谨慎考虑。