oracle 9i下線上重定義表

9i提供了聯機重定義表的方法，可以讓你在基本不影響原表的DML情況下修改表結構。

實際上，聯機重定義表並不是完全的聯機重定義，在最後交換表名的時候會短暫地鎖定原表和中間表，但這個過程很短暫，相對於傳統方法來說，這是一個進步。

/*
9i提供了聯機重定義表的方法，可以讓你在基本不影響原表的DML情況下修改表結構。

實際上，聯機重定義表並不是完全的聯機重定義，在最後交換表名的時候會短暫地鎖定原表和中間表，但這個過程很短暫，相對於傳統方法來說，這是一個進步。

利用聯機重定義功能可以線上實現如下功能：
修改表的儲存參數
移動該表到相同 Schema 下的 不同資料表空間內
添加並行查詢支援
添加或刪除分區
重建表以便減少片段
在普通表和索引組織(index-organized)表之間互相轉換
添加或刪除列，重新定義列的資料類型
添加/刪除索引
做一個從普通表到分區表之間的轉換操作.
修改約束

需要的許可權：
CREATE ANY TABLE
ALTER ANY TABLE
DROP ANY TABLE
LOCK ANY TABLE
SELECT ANY TABLE

限制條件：
·你必須有足以維護兩份表格拷貝的空間。
·你不能更改主鍵欄。
·表格必須有主鍵。
·必須在同一個大綱中進行表格重定義。
·在重定義操作完成之前，你不能對新加欄加以NOT NULL約束。
·表格不能包含LONG、BFILE以及使用者類型（UDT）。
·不能重定義鏈表（clustered tables）。
·不能在SYS和SYSTEM大綱中重定義表格。
·不能用具體化檢視日誌（materialized view logs）來重定義表格；不能重定義含有具體化檢視的表格。
·不能在重定義過程中進行橫向分集（horizontal subsetting）。

基本步驟如下

第一步：利用dbms_redefinition.can_redef_table過程檢查該表是否能被線上重定義。
如果這一步不拋出異常，說明該表是可以線上重定義的。

第二步：建立一個與原表類似的空表結構，用於重定義該表，這裡叫做是中間表
在這裡你可以定義表的新列名、新資料類型、列順序、儲存參數等。注意，為了提高效率，在這一步不要建立索引和約束。

第三步：用dbms_redefinition.start_redef_table procedure定義重構開始

這個過程將會自動執行如下操作：
1、插入所有行從原有表到中間表
2、建立MLOG$_xxx快照與快照日誌，臨時儲存DML語句直到完成。

該過程可以輸入如下參數資訊
. 使用者的名稱
. 原表的名稱
. 中間表的名稱
. 原表和中間表的列的映射關係。
這個參數預設值是null，表示原表和中間表的欄位按原表的順序一一對應。但如果需要在中間表添加、刪除欄位，修改欄位資料類型、改變欄位順序，則必須要把這個參數填寫好。不同欄位間用都好分隔。
作映射時，對應的欄位先寫原表欄位，然後再寫中間表的欄位。
如添加欄位，並改變列的資料類型：
原表 a(id int ,name varchar2(10))
中間表b(id number,new_name varchar2(10),sex int)
exec dbms_redefinition.start_redef_table procedure('suk','a','b','name new_name,id id')
或者exec dbms_redefinition.start_redef_table procedure('suk','a','b','id id,name new_name')

再如 同時添加、刪除欄位：
原表 a(id int ,name varchar2(10))
中間表b(id number,sex int)
exec dbms_redefinition.start_redef_table procedure('suk','a','b','id id')

映射時可以使用一些簡單函數，如
exec dbms_redefinition.start_redef_table procedure('suk','a','b','to_char(id) id')

所以，如果資料量很大的話，這一步會比較慢。
第四步：調用DBMS_REDEFINITION.SYNC_INTERIM_TABLE過程同步原表與中間表的資料
這一步不是必須的，如果省略這一步，在finish_redef_table也會執行這一步驟。但我們應該把這一步放在為中間表建立索引、約束等前面，這樣可以提高效率。

第五步：與原表一致，在中間表上面建立約束，索引，觸發器
與原表一致（如果需要），中間表的對象許可權被授予給別的對象
注意：在中間表建立外鍵約束時應該加上DISABLE關鍵字

第六步：用dbms_redefinition.finish_redef_table過程完成表的最終重定義
該過程將自動完成
. 應用快照日誌中的DML到中間表
. 互換原表與中間表的名字，包括所有可能出現的資料字典
. 但是需要注意的是，並不對換約束，索引，觸發器的名稱，這些需要手工修改
. 刪除MLOG$_XXX
. 啟用原來在中間表上的外鍵

同時，執行這一步時，oracle會短暫地LOCK原表和中間表

第七步：刪除中間表、

第八步：如果是920以上，可以利用ALTER TABLE ... RENAME CONSTRAINT ...語句來修改約束名稱，如果以下版本，就只有刪除並重建了，當然，如果約束名稱並不重要，也就無所謂了

第九步：如果重組織失敗，那麼你就必須採取特殊的步驟來讓它重新開始。由於重定義過程需要建立表格的快照，因此為了重新開始這一過程，你必須調用DBMS_REDEFINITION.ABORT_REDEF_TABLE來釋放快照。

幾個需要注意的地方：
. 在重定義表期間，不允許對中間表作任何DML操作
*/

--以下是一個利用9i的聯機重定義表刪除欄位的簡單過程

--原表結構
SQL> desc channel
Name Type Nullable Default Comments
------------------- -------------- -------- ------- --------
CHANNEL_ID NUMBER(16)
CHANNEL_NAME VARCHAR2(120)
PARENT_CHANNEL_ID NUMBER(16) Y
CHANNEL_URL VARCHAR2(256) Y
CHANNEL_DESCRIPTION VARCHAR2(4000) Y
STYLE_CLASS VARCHAR2(120) Y
CONFIGURE_FILE VARCHAR2(120) Y
CHANNEL_SEQUENCE NUMBER(4) Y 9999
CHANNEL_LEVEL NUMBER(2) Y
CREATE_DATE DATE SYSDATE
SELF_TAG_ID NUMBER(8) Y
CHANNEL_CODE VARCHAR2(120) Y

--建立中間表
SQL> create table channel_test as select * from channel where 1=2;

Table created

SQL> alter table channel_test drop column STYLE_CLASS;

Table altered

SQL> desc channel_test
Name Type Nullable Default Comments
------------------- -------------- -------- ------- --------
CHANNEL_ID NUMBER(16)
CHANNEL_NAME VARCHAR2(120)
PARENT_CHANNEL_ID NUMBER(16) Y
CHANNEL_URL VARCHAR2(256) Y
CHANNEL_DESCRIPTION VARCHAR2(4000) Y
CONFIGURE_FILE VARCHAR2(120) Y
CHANNEL_SEQUENCE NUMBER(4) Y
CHANNEL_LEVEL NUMBER(2) Y
CREATE_DATE DATE
SELF_TAG_ID NUMBER(8) Y
CHANNEL_CODE VARCHAR2(120) Y

--檢測是否能線上重定義表
SQL> exec dbms_redefinition.can_redef_table('suk','channel');

PL/SQL procedure successfully completed

--開始重定義表
SQL> exec dbms_redefinition.start_redef_table('suk','channel','channel_test','channel_id,CHANNEL_NAME,PARENT_CHANNEL_ID,CHANNEL_URL,CHANNEL_DESCRIPTION,CONFIGURE_FILE,CHANNEL_SEQUENCE,CHANNEL_LEVEL,CREATE_DATE,SELF_TAG_ID,CHANNEL_CODE');

PL/SQL procedure successfully completed

--完成重定義表
SQL> exec dbms_redefinition.finish_redef_table('suk','channel','channel_test');

PL/SQL procedure successfully completed

--查看重定義後的表結構，可以看到表channel的結構已經改變
SQL> desc channel
Name Type Nullable Default Comments
------------------- -------------- -------- ------- --------
CHANNEL_ID NUMBER(16)
CHANNEL_NAME VARCHAR2(120)
PARENT_CHANNEL_ID NUMBER(16) Y
CHANNEL_URL VARCHAR2(256) Y
CHANNEL_DESCRIPTION VARCHAR2(4000) Y
CONFIGURE_FILE VARCHAR2(120) Y
CHANNEL_SEQUENCE NUMBER(4) Y
CHANNEL_LEVEL NUMBER(2) Y
CREATE_DATE DATE
SELF_TAG_ID NUMBER(8) Y
CHANNEL_CODE VARCHAR2(120) Y

參考文檔：

piner 9i新特性之——線上表格重定義研究

tahiti.oracle.com