11g中關於表新增欄位default屬性研究

在客戶現場碰到一個問題，客戶在業務期間，對一張非常大的表新增了一個欄位，並且採用default欄位預設非空，從而導致表被鎖定，undo被大量消耗，資源被大量佔用，接下來客戶主動掐掉了回話，導致資料庫回滾，資料庫直接無法連線。回滾結束後資料庫資源釋放，系統正常。重新新增欄位，預設欄位為空，新增成功。回想了一下，其實有幾點是不正確的，首先，最好不要在正常的業務期間對錶做大變更，特別是一張大表。其次新增欄位時要考慮謹慎，不要新增相關的default引數，這也是引起本次事故的原因，導致該表被直接鎖定，業務無法正常訪問。最好的辦法應該是先新增列，在業務空閒的時候執行對列的修改：alter table table_name add col_name data_type;
atler table table_name modify col_name default default_value;
在後來我想如果回滾時間非常久，那資料庫資源一直得不到釋放，那資料庫不是一直無法連線了，於是在網上找找看看有什麼相關的應急辦法。找到11G新特性中關於這個問題的解決辦法。
在11g中，可以直接修改資料字典將已有行的default值更新 而無需修改實際的表記錄。在10g中一旦新增某表並使用default引數，資料庫就會鎖定該表，並更新所有的行。而在11g中
我們採用alter table table_name add col_name data_type default 'XX' not null;時，oracle會將預設值寫到資料字典(sys.col$.default$)中，這樣，當我們新增了一個新列時，對於以前原有的資料，oracle採用資料字典中的資訊來表達，而對於新新增進來的列，則採用預設值寫入，這樣我們就可以將一個新列定義為非空並具有預設值，同時不會導致任何重做和undo開銷。本次測試旨在對該新特性有一個基本的瞭解。不做深入分析。
testing：
會話一：
SQL> create table test(a number);
Table created.
SQL> insert into test values(1);
1 row created.
SQL> insert into test values(2);
1 row created.

會話二：
SQL> conn frank/frank
Connected.
SQL> select * from test;
no rows selected
SQL> desc test;
 Name                                      Null?    Type
 —————————————– ——– —————————-
 A                                                  NUMBER
SQL> alter table test add(b number default 10);
alter table test add(b number default 10)
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL> alter table test add(b number default 10 not null);

（回話被hang住）

會話一：
SQL> commit;
Commit complete.
SQL>
會話二：
SQL> alter table test add(b number default 10 not null);
Table altered.

此時我們可以在回話一種查詢一下：
SQL> select * from test;
         A          B
———- ———-
         1         10
         2         10
預設欄位被新增進入，我們在查詢時，oracle內部其實對於已經存在的資料是不在做修改，它將從資料字典(sys.col$.default$)獲取預設值並將其返回給使用者，我們可以看一下儲存的資料：
SQL> select object_name,object_id from dba_objects where object_name='TEST';

OBJECT_NAM  OBJECT_ID
———- ———-
TEST            18458

SQL> select OBJ#,COL#,NAME,DEFAULT$ from col$ where OBJ#=18458;

      OBJ#       COL# NAME       DEFAULT$
———- ———- ———- ———-
     18458          1 A
     18458          2 B          10

我們可以看到，預設的default=10被寫入了ecl$基表中，當我們做查詢時。oracle其實是從這一部分取出值來應用。

本次資料庫實驗環境是11gR2 我們可以看出如果有其他事務未作提交，那麼當我新增具有default的值的列式如果是不是not null 的新列，是會報ORA-00054資源繁忙錯誤的，而如果新增not null時，則資料庫會hang住。（這和11gR1不一樣，在11gR1（11.1.0.6）中測試時，是可以成功的，可能對於R2做了改變），此時我們提交回話一的事務，則回話2獲得資源，會話也成功。
也就是說11g 後可以直接修改資料字典將已有行的default值更新 而無需修改實際的表記錄，他已經可以做到實時應用而不會因為表很大而造成效能問題。