分割槽表在各行業的資料庫都得到廣泛應用，但是有些業務系統在設計階段對系統資料和效能容量增長估計不足，或沒有考慮到運維過程中的資料歸檔需求，往往沒有對錶做分割槽設計。在生產執行經過長時間的資料積累之後，才發現表越來越大，某些查詢或插入資料的效能變得越來越慢，迫切需要做表分割槽改造。所以在生產系統運維過程中，經常遇到的一個需求是如何把一個資料量非常大的普通表改造成分割槽表。關於分割槽表的介紹，請參考博主的另一篇博文：http://blog.itpub.net/31015730/viewspace-2144183/

分割槽最早在oracle8.0版本引入，支援將一個表或索引物理地分解為多個更小、更可管理的部分。Oracle9i之前，表資料的整理是透過 alter table XXX move [tablespace XXX]進行的。如果表非常大，IO又不快的時候，move的過程可能相當漫長，不能算是HA特性。因此在HA的線上維護中，基本不會利用move來重組大型表，而且move後的表需要重建索引。在高可用系統中，如果需要改變一個表的定義是一件比較棘手的問題，尤其是對於7×24系統。Oracle提供的基本語法基本可以滿足一般性修改，但是對於把普通堆表改為分割槽表，把索引組織表修改為堆表等操作就無法完成了。那麼問題來了，業務系統往往都是7*24線上作業，改造的過程又必然涉及表結構的變動，如果對錶進行重建，會對系統執行產生非常大的影響，通常會設定計劃停機視窗來做這類維護操作。

將普通表改造為分割槽表主要有四種方式：

Export/import method

Insert with a subquery method

Partition exchange method

DBMS_REDEFINITION

具體參考：
How to Partition a Non-partitioned Table [ID 1070693.6]
http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218704.aspx

在本篇博文中主要介紹第四種方式：線上重定義，線上重定義技術允許資料庫管理員在該表上有讀寫資料操作的情況下，非常靈活地修改表的物理屬性、表資料、表結構。

更多關於DBMS_REDEFINITION的方法請參看作者另一篇博文：http://blog.itpub.net/31015730/viewspace-2144516/

線上重定義的應用場景和功能：

1、修改表的儲存引數；

2、可以將錶轉移到其他表空間；

3、增加並行查詢選項；

4、增加或刪除分割槽；

5、重建表以減少碎片；

6、將堆表改為索引組織表或相反的操作；

7、增加或刪除一個列。

呼叫DBMS_REDEFINITION包需要 EXECUTE_CATALOG_ROLE 角色，除此之外，還需要CREATE ANY TABLE、ALTER ANY TABLE、DROP ANY TABLE、LOCK ANY TABLE和SELECT ANY TABLE的許可權。

線上重定義表的步驟如下：

1.選擇一種重定義方法：

存在兩種重定義方法，一種是基於主鍵、另一種是基於ROWID。ROWID的方式不能用於索引組織表，而且重定義後會存在隱藏列M_ROW$$。預設採用主鍵的方式。

2.呼叫DBMS_REDEFINITION.CAN_REDEF_TABLE()過程，如果表不滿足重定義的條件，將會報錯並給出原因。

3.在用一個方案中建立一個空的中間表，根據重定義後你期望得到的結構建立中間表。比如：採用分割槽表，增加了COLUMN等。

4.呼叫DBMS_REDEFINITION.START_REDEF_TABLE()過程，並提供下列引數：被重定義的表的名稱、中間表的名稱、列的對映規則、重定義方法。

如果對映方法沒有提供，則認為所有包括在中間表中的列用於表的重定義。如果給出了對映方法，則只考慮對映方法中給出的列。如果沒有給出重定義方法，則認為使用主鍵方式。

5.在中間表上建立觸發器、索引和約束，並進行相應的授權。任何包含中間表的完整性約束應將狀態置為disabled。

當重定義完成時，中間表上建立的觸發器、索引、約束和授權將替換重定義表上的觸發器、索引、約束和授權。中間表上disabled的約束將在重定義表上enable。

6.（可選）如果在執行DBMS_REDEFINITION.START_REDEF_TABLE()過程和執行DBMS_REDEFINITION.FINISH_REDEF_TABLE()過程直接在重定義表上執行了大量的DML操作，那麼可以選擇執行一次或多次的SYNC_INTERIM_TABLE()過程，以減少最後一步執行FINISH_REDEF_TABLE()過程時的鎖定時間。

7.執行DBMS_REDEFINITION.FINISH_REDEF_TABLE()過程完成表的重定義。這個過程中，原始表會被獨佔模式鎖定一小段時間，具體時間和表的資料量有關。

執行完FINISH_REDEF_TABLE()過程後，原始表重定義後具有了中間表的屬性、索引、約束、授權和觸發器。中間表上disabled的約束在原始表上處於enabled狀態。

8.（可選）可以重新命名索引、觸發器和約束。對於採用了ROWID方式重定義的表，包括了一個隱含列M_ROW$$。推薦使用下列語句經隱含列置為UNUSED狀態或刪除。

ALTER TABLE TABLE_NAME SET UNUSED (M_ROW$$);

ALTER TABLE TABLE_NAME DROP UNUSED COLUMNS;

下面是進行重定義操作後的結果：

原始表根據中間表的屬性和特性進行重定義；

START_REDEF_TABLE()和FINISH_REDEF_TABLE()操作之間在中間表上建立的觸發器、索引、約束和授權，現在定義在原始表上。中間表上disabled的約束在原始表上處於enabled狀態。

原始表上定義的觸發器、索引、約束和授權建立在中間表上，並會在刪除中間表時刪除。原始表上原來enabled狀態的索引，建立在中間表上，並處於disabled狀態。

任何定義在原始表上的儲存過程和遊標都會變為INVALID，當下次呼叫時後自動進行編譯。

如果執行過程中出現錯誤或者人為選擇退出的話，可以執行DBMS_REDEFINITION.ABORT_REDEF_TABLE()過程。

其中UNAME 引數是指使用者；

Oracle的普通表沒有辦法透過修改屬性的方式直接轉化為分割槽表，必須透過重建的方式進行轉變；

下面，我們透過一個簡單的例子，去看看如何使用dbms_redefinition包進行重定義操作。

實驗環境：
作業系統：CentOS 7.1
資料庫：Oracle 11.2.0.4

需求;在seiang使用者下的t_wjq表上，增加多個欄位

--在seiang使用者下建立目標資料表t_wjq

SEIANG@seiang11g>select count(*) from dba_objects;

COUNT(*)

----------

86990

SEIANG@seiang11g>set timing on

SEIANG@seiang11g>create table t_wjq as select object_id,object_name,owner from dba_objects;

Table created.

Elapsed: 00:00:00.36

--檢視錶中的資料量

SEIANG@seiang11g>select count(*) from dba_objects;

COUNT(*)

----------

86991

Elapsed: 00:00:00.07

SEIANG@seiang11g>select count(*) from t_wjq;

COUNT(*)

----------

86991

Elapsed: 00:00:00.01

--在t_wjq表的object_id列上建立主鍵

SEIANG@seiang11g>alter table t_wjq add constraint pk_t_wjq_id primary key(object_id);

Table altered.

Elapsed: 00:00:00.26

--檢視索引

SEIANG@seiang11g>select index_name,index_type,table_owner,table_name from user_indexes;

INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME

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

PK_T_WJQ_ID NORMAL SEIANG T_WJQ

Elapsed: 00:00:00.22

--進行線上重定義的第一步，就是判斷目標資料表是否可以進行重定義。可以使用dbms_redefinition包的can_redef_table方法進行判斷

SEIANG@seiang11g>exec dbms_redefinition.can_redef_table( 'SEIANG','T_WJQ',dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.18

注意該方法的第三個引數，使用主鍵還是rowid方法。本質上，Online Redefinition是使用物化檢視Materialized View技術。過程定義記錄就是主鍵和rowid兩種策略。通常而言，我們還是推薦資料表有一個明確主鍵，也就是使用cons_use_pk。如果希望使用rowid，就使用dbms_redefinition.cons_use_rowid。

--透過了檢查之後，就可以進行下一步，定義目標資料表格式。無論是何種變化，我們需要建立一箇中間表t_wjq_interim，將我們“期望”的資料表定義實現在裡面。其中包括表型別、列定義、分割槽定義和索引等。但是注意，約束（主外來鍵）可以不定義在其中。

SEIANG@seiang11g>create table t_wjq_interim as select * from dba_objects where 1=0;

Table created.

Elapsed: 00:00:00.08

--檢視錶結構

SEIANG@seiang11g>desc t_wjq

Name Null? Type

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

OBJECT_ID NOT NULL NUMBER

OBJECT_NAME VARCHAR2(128)

OWNER VARCHAR2(30)

SEIANG@seiang11g>

SEIANG@seiang11g>desc t_wjq_interim

Name Null? Type

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

OWNER VARCHAR2(30)

OBJECT_NAME VARCHAR2(128)

SUBOBJECT_NAME VARCHAR2(30)

OBJECT_ID NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(19)

CREATED DATE

LAST_DDL_TIME DATE

TIMESTAMP VARCHAR2(19)

STATUS VARCHAR2(7)

TEMPORARY VARCHAR2(1)

GENERATED VARCHAR2(1)

SECONDARY VARCHAR2(1)

NAMESPACE NUMBER

EDITION_NAME VARCHAR2(30)

顯然，我們是希望給資料表T增加一些列。

--開始重定義過程。使用dbms_redefinition的start_redef_table方法

SEIANG@seiang11g>exec dbms_redefinition.start_redef_table('SEIANG','T_WJQ','T_WJQ_INTERIM',col_mapping => 'object_id object_id, object_name object_name, owner owner',options_flag => dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.10

此處注意引數col_mapping，這裡以配對鍵值的方式定義了源資料表和中間表在列關係上的對應關係。每個列關係按照<源列名目標列名>的格式進行書寫。

--對約束的處理，如果有需要轉換的約束物件，我們可以使用copy_table_dependents方法將source資料表的約束複製到目標物件中。

SEIANG@seiang11g>declare

2 error_count number;

3 begin

4 error_count := 0;

5 dbms_redefinition.copy_table_dependents(

6 uname => 'SEIANG',

7 orig_table => 'T_WJQ',

8 int_table => 'T_WJQ_INTERIM',

9 copy_indexes => dbms_redefinition.cons_orig_params,

10 num_errors => error_count);

11 dbms_output.put_line(to_char(error_count)

12 );

13 end;

14 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.89

利用各種copy_xxx引數，我們可以精細的定義哪些約束依賴關係會被複製到目標表中。

--當結束之後，我們需要使用finish_redef_table方法結束定義過程。

SEIANG@seiang11g>exec dbms_redefinition.finish_redef_table('SEIANG','T_WJQ','T_WJQ_INTERIM');

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.76

--重定義結束，再次檢視錶結構

SEIANG@seiang11g>desc t_wjq

Name Null? Type

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

OWNER VARCHAR2(30)

OBJECT_NAME VARCHAR2(128)

SUBOBJECT_NAME VARCHAR2(30)

OBJECT_ID NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(19)

CREATED DATE

LAST_DDL_TIME DATE

TIMESTAMP VARCHAR2(19)

STATUS VARCHAR2(7)

TEMPORARY VARCHAR2(1)

GENERATED VARCHAR2(1)

SECONDARY VARCHAR2(1)

NAMESPACE NUMBER

EDITION_NAME VARCHAR2(30)

SEIANG@seiang11g>

SEIANG@seiang11g>desc t_wjq_interim

Name Null? Type

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

OBJECT_ID NOT NULL NUMBER

OBJECT_NAME VARCHAR2(128)

OWNER VARCHAR2(30)

--檢視相關索引

SEIANG@seiang11g>select index_name,index_type,table_owner,table_name from user_indexes;

INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME

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

TMP$$_PK_T_WJQ_ID0 NORMAL SEIANG T_WJQ_INTERIM

PK_T_WJQ_ID NORMAL SEIANG T_WJQ

Elapsed: 00:00:00.23

關於對線上重定義過程中的細節，例如實現方式、中間DML操作同步、鎖機制和各種方法的功能原理，將在作者接下來的部落格中繼續進行討論。

作者：SEian.G（苦練七十二變，笑對八十一難）

Online Redefinition線上重定義（一）

相關文章