ORACLE 11g新特性-允許DDL鎖等待DML鎖

之前也寫了一些關於ORACLE11g新特性的文章，現在ORACLE 11g已成為主流的ORACLE資料庫版本，瞭解和學習ORACLE 11g的新特性至關重要，本人也是ORACLE 11g新特性的初學者，在此分享下我的學習過程和心得。

本文主要記錄的是ORACLE 11g的一個新特性，允許DDL鎖等待DML鎖，這也是在6月30日，張樂奕（kamus）老師在ACOUG活動中分享的一個主題。

在11g之前的版本，預設情況下，DDL鎖都不等待DML鎖，在一個存在DML鎖的表上執行DDL操作，會立即返回失敗（同一SESSION除外）,下面簡單做下試驗，在SESSION1向表STREAM中插入資料，不要提交，此時表STREAM會存在DML鎖，在SESSION2執行TRUNCATE表STREAM操作，就會立即返回失敗：

SESSION1 >insert into stream select * from dbdream;

10 rows created.

SESSION2 >truncate table stream;

truncate table stream

               *

ERROR at line 1:

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

       在11g版本，ORACLE推出了DDL_LOCK_TIMEOUT引數，允許DDL鎖等待DML鎖，該引數控制DDL鎖等待DML鎖的時間，單位為秒，預設值為0，即DDL鎖不等待DML鎖，最大值是100萬，也就是11.5天，該引數可以全域性設定，也可以在SESSION級設定。

SESSION2 >show parameter ddl_lock_timeout

NAME                    TYPE        VALUE

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

ddl_lock_timeout        integer     0

SESSION2 >alter session set ddl_lock_timeout=2000000;

ERROR:

ORA-00068: invalid value 2000000 for parameter ddl_lock_timeout, must be

between 0 and 1000000

    下面演示下DDL_LOCK_TIMEOUT引數的效果，還拿以上STREAM表為例，目前STREAM表仍然存在DML鎖，在SESSION2將DDL_LOCK_TIMEOUT設定為60秒，然後再執行TRUNCATE操作，此時不會馬上返回失敗，而會等待60秒的時間，如果60秒內STREAM表的DL鎖沒有釋放，才會返回失敗。

SESSION2 >alter session set ddl_lock_timeout=60;

Session altered.

SESSION2 >set timing on         

SESSION2 >truncate table stream;

--此時，SESSION2掛起，在60秒的時間內等待SESSION1釋放DML鎖，60秒後返回失敗

truncate table stream

               *

ERROR at line 1:

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

Elapsed: 00:01:00.01

    DDL_LOCK_TIMEOUT引數的出現，極大簡化了DBA的操作，比如我們要在一張表上增加個欄位，可是這張表有大量的DML操作，並且我們不能使用ALTER SYSTEM KILL SESSION的方法結束這些操作，那麼我們想要在這張表上增加欄位就需要無數次的執行增加欄位的操作，或者寫指令碼去幹這個活，現在有了DDL_LOCK_TIMEOUT引數，我們只需要將這個引數的值設定的稍大一點，執行一下SQL就不用管啦，下面演示下這個過程。

SESSION2增加欄位：

SESSION2 >alter table stream add jpg_path varchar2(255) default '/home/oracle/';

此時掛起，等待STREAM表的DML鎖釋放，SESSION1提交釋放DML鎖後，SESSION2操作便成功了。

SESSION1 >commit;

Commit complete.

SESSION2 >alter table stream add jpg_path varchar2(255) default '/home/oracle/';

Table altered.

SESSION2 >select * from stream;

        ID IDENTIFIER      PIC_NO JPG_PATH

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

         1 18-0220-003     1      /home/oracle/

         2 18-0221-003     1      /home/oracle/

         3 18-0221-003     2      /home/oracle/

         4 18-0221-003     3      /home/oracle/

         5 18-0223-005     1      /home/oracle/

         6 18-0223-005     2      /home/oracle/

         7 18-0223-005     3      /home/oracle/

         8 18-0223-005     4      /home/oracle/

         9 18-0223-005     5      /home/oracle/

        10 18-0223-005     6      /home/oracle/

10 rows selected.