Oracle11新特性——線上操作功能增強（一）

打算寫一系列的文章介紹11g的新特性和變化。

11g中線上處理功能得到了很大增強，其中包括線上修改表結構，線上建立或重建索引，建立不可見索引，表增加非空欄位，線上DDL以及物件依賴性細化等。

這一篇介紹一下可等待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順利完成。

這對於繁忙的系統可能造成很嚴重的問題，設定之前需要謹慎考慮。