Online Redefinition線上重定義（二）--單表複雜案例

在上一篇博文（Online Redefinition線上重定義（一））中，簡單地介紹了Oracle線上重定義特性進行資料表線上結構變動操作。本篇博文將演示一個較複雜的案例，在案例的複雜變化中進行線上重定義及dbms_redefinition包各個關鍵方法的作用。

普通表改造分割槽表

--建立一張普通表t_wjq1

SEIANG@seiang11g>create table t_wjq1 as select object_id,object_name,created from dba_objects;

Table created.

SEIANG@seiang11g>desc t_wjq1

 Name                                                                                                              Null?    Type

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

 OBJECT_ID                                                                                                                NUMBER

 OBJECT_NAME                                                                                                              VARCHAR2(128)

 CREATED                                                                                                                  DATE

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

  COUNT(*)

----------

     86997

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

SEIANG@seiang11g>alter table t_wjq1 add constraint pk_t_wjq1_id primary key(object_id);

Table altered.

SEIANG@seiang11g>

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

INDEX_NAME                     INDEX_TYPE                  TABLE_OWNER                    TABLE_NAME

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

PK_T_WJQ1_ID                   NORMAL                      SEIANG                         T_WJQ1

需求：重定義的內容有以下幾個：
（1）使用object_id進行分割槽
（2）created欄位從date型別變為timestamp型別
（3）object_name欄位改名為object_name_2。

--建立一張中間表t_wjq1_interim

SEIANG@seiang11g>create table t_wjq1_interim(

  2     object_id number,

  3     object_name_2 varchar2(128),

  4     created timestamp

  5  )

  6  partition by range(object_id)

  7  (

  8     partition p1 values less than (5000),

  9     partition p2 values less than (10000),

 10     partition p3 values less than (50000),

 11     partition p4 values less than (maxvalue)

 12  );

Table created.

--檢視中間表的分割槽情況

SEIANG@seiang11g>select table_name,partition_name from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME

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

T_WJQ1_INTERIM                 P4

T_WJQ1_INTERIM                 P3

T_WJQ1_INTERIM                 P2

T_WJQ1_INTERIM                 P1

--首先，檢視t_wjq1表是否支援重定義操作

SEIANG@seiang11g>exec dbms_redefinition.can_redef_table('SEIANG','T_WJQ1',options_flag=>dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

--開始重定義操作

SEIANG@seiang11g>exec dbms_redefinition.start_redef_table('SEIANG','T_WJQ1','T_WJQ1_INTERIM',col_mapping => 'object_id object_id, object_name object_name_2, to_timestamp(created) created',options_flag => dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.42

注意這個col_mapping對映關係設定，如果存在列名轉換，就在這裡將列關係對映說明出來。如果需要進行欄位型別轉換，要書寫函式關係將對映計算規則定義出來。

Oracle線上重定義的基礎是物化檢視。此時，通過檢視試圖user_mviews，可以看到有一個新的物化檢視生成，並且存在對應的物化檢視日誌。

SEIANG@seiang11g>select mview_name, container_name, query, REFRESH_METHOD from user_mviews;

MVIEW_NAME                     CONTAINER_NAME                 QUERY                                                          REFRESH_

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

T_WJQ1_INTERIM                 T_WJQ1_INTERIM                 select object_id object_id, object_name object_name_2, to_timestamp(created) cre FAST

Elapsed: 00:00:00.02

SEIANG@seiang11g>

SEIANG@seiang11g> select master,log_table from user_mview_logs;

MASTER                         LOG_TABLE

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

T_WJQ1                         MLOG$_T_WJQ1

Elapsed: 00:00:00.00

線上重定義的Start方法建立了一個Fast重新整理模式的物化檢視物件t_wjq1_interim。物化檢視中最重要的物化檢視日誌，名稱為MLOG$_T_WJQ1

--檢視原始表和中間表的資料量

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

  COUNT(*)

----------

     86997

Elapsed: 00:00:00.01

SEIANG@seiang11g>

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

  COUNT(*)

----------

     86997

Elapsed: 00:00:00.01

--沒有DML操作，所以物化檢視日誌尚空

SEIANG@seiang11g>select * from mlog$_t_wjq1;

no rows selected

Elapsed: 00:00:00.00

SEIANG@seiang11g>select count(*) from mlog$_t_wjq1;

  COUNT(*)

----------

         0

Elapsed: 00:00:00.00

綜合上述內容，說明start_redef_table的作用是下面幾個方面：

（1）以Interim資料表為名稱，建立一個Fast重新整理模式的物化檢視物件；

（2）從源資料表中將資料載入到Interim中；

（3）建立物化檢視日誌；

如果在這個過程中，發生了DML操作，也就是說在start過程或者之後有DML操作，有新資料插入或修改，如下操作所示：

--檢視t_wjq1表的資料量和最大的object_id值

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

  COUNT(*)

----------

     86997

Elapsed: 00:00:00.00

SEIANG@seiang11g>select max(object_id) from t_wjq1;

MAX(OBJECT_ID)

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

         89700

Elapsed: 00:00:00.01

--模擬DML操作：在表t_wjq1中插入資料庫

SEIANG@seiang11g>insert into t_wjq1 select object_id+90000,object_name,created from dba_objects;

87006 rows created.

Elapsed: 00:00:05.13

--再次檢視原始表t_wjq1、中間表t_wjq1_interim以及物化檢視日誌試圖的變化

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

  COUNT(*)

----------

    174003

Elapsed: 00:00:00.01

SEIANG@seiang11g>

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

  COUNT(*)

----------

     86997

Elapsed: 00:00:00.00

SEIANG@seiang11g>select count(*) from mlog$_t_wjq1;

  COUNT(*)

----------

     87006

Elapsed: 00:00:00.01

發現：中間表的資料內容保持不變，並且物化檢視日誌積累了需要重新整理的資料條目。此時存在資料的不一致和不統一。Oracle推薦要求使用sysnc_interim_table方法將重定義過程中出現的變化資料重新整理。

--重新整理8萬多條資料，使用了超過四分鐘時間。在這個過程中，我們可以看到重新整理物化檢視過程。

SEIANG@seiang11g>exec dbms_redefinition.sync_interim_table('SEIANG','T_WJQ1','T_WJQ1_INTERIM');

PL/SQL procedure successfully completed.

Elapsed: 00:04:18.33

SEIANG@seiang11g>select * from v$mvrefresh;

       SID    SERIAL# CURRMVOWNER                     CURRMVNAME

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

        41      14059 SEIANG                          T_WJQ1_INTERIM

--重新整理開始和結束過程，我們可以看到物化檢視重新整理過程中的時間變化。

SEIANG@seiang11g>alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

Session altered.

SEIANG@seiang11g>

SEIANG@seiang11g>select name, LAST_REFRESH from user_mview_refresh_times;

NAME                           LAST_REFRESH

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

T_WJQ1_INTERIM                 2017-09-06 13:59:57

SEIANG@seiang11g>select name, LAST_REFRESH from user_mview_refresh_times;

NAME                           LAST_REFRESH

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

T_WJQ1_INTERIM                 2017-09-06 14:22:20

--重新整理結束後，發現t_wjq1_interim表和mlog$_t_wjq1日誌表資料的變化

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

  COUNT(*)

----------

    174003

SEIANG@seiang11g>

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

  COUNT(*)

----------

    174003

SEIANG@seiang11g>

SEIANG@seiang11g>select count(*) from mlog$_t_wjq1;

  COUNT(*)

----------

         0

說明無變化資料需要重新整理了

綜合上面的實驗，知道方法sync_interim_table的實質是進行一次物化檢視快速重新整理。這個方法持續的時間根據不同資料量和物化檢視重新整理演算法來決定，這個過程中，並不會引起很多鎖定動作。而且，線上重定義過程中，這個方法是可以重複執行多次的。

--下面將原有資料表中的約束關係重新整理到目標結構上

SEIANG@seiang11g>declare

  2     error_count number:=0;

  3  begin

  4     dbms_redefinition.copy_table_dependents(uname => 'SEIANG',orig_table => 'T_WJQ1',int_table => 'T_WJQ1_INTERIM',

  5     copy_indexes => dbms_redefinition.cons_orig_params,

  6     num_errors => error_count);

  7     dbms_output.put_line(to_char(error_count));

  8  end;

  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.29

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_WJQ1_ID0            NORMAL                      SEIANG                         T_WJQ1_INTERIM

PK_T_WJQ1_ID                   NORMAL                      SEIANG                         T_WJQ1

I_MLOG$_T_WJQ1                 NORMAL                      SEIANG                         MLOG$_T_WJQ1

Elapsed: 00:00:00.04

SEIANG@seiang11g>select master,log_table from user_mview_logs;

MASTER                         LOG_TABLE

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

T_WJQ1                         MLOG$_T_WJQ1

Finish過程主要完成六個步驟操作：

（1）執行sysnc_interim_table命令，將中間表資料儘可能靠近源資料表；

（2）鎖定源資料表T，使之後不能有任何變化發生在這個資料表上；

（3）再次執行sysnc_interim_table命令，這個時候執行的時間不會很長；

（4）將源資料表和Interim資料表表名進行置換；

（5）登出unregistered物化檢視，並且刪除掉物化檢視日誌；

（6）釋放開在中間表上的鎖定；

SEIANG@seiang11g> exec dbms_redefinition.finish_redef_table('SEIANG','T_WJQ1','T_WJQ1_INTERIM');

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.11

SEIANG@seiang11g>select master,log_table from user_mview_logs;

no rows selected

Elapsed: 00:00:00.02

SEIANG@seiang11g>

SEIANG@seiang11g>select * from mlog$_t_wjq1;

select * from mlog$_t_wjq1

              *

ERROR at line 1:

ORA-00942: table or view does not exist

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_WJQ1_ID0            NORMAL                      SEIANG                         T_WJQ1_INTERIM

PK_T_WJQ1_ID                   NORMAL                      SEIANG                         T_WJQ1

--檢查重定義的結果

SEIANG@seiang11g>desc t_wjq1

 Name                                                                                                              Null?    Type

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

 OBJECT_ID                                                                                                                NUMBER

 OBJECT_NAME_2                                                                                                            VARCHAR2(128)

 CREATED                                                                                                                  TIMESTAMP(6)

SEIANG@seiang11g>

SEIANG@seiang11g>desc t_wjq1_interim

 Name                                                                                                              Null?    Type

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

 OBJECT_ID                                                                                                         NOT NULL NUMBER

 OBJECT_NAME                                                                                                              VARCHAR2(128)

 CREATED                                                                                                                  DATE

SEIANG@seiang11g> exec dbms_stats.gather_table_stats(user,'T_WJQ1',cascade => true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.76

--分割槽和主鍵物件實現成功

SEIANG@seiang11g>select table_name,partition_name from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME

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

T_WJQ1                         P1

T_WJQ1                         P2

T_WJQ1                         P3

T_WJQ1                         P4

SEIANG@seiang11g>select constraint_name,constraint_type,table_name from user_constraints;

CONSTRAINT_NAME                C TABLE_NAME

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

TMP$$_PK_T_WJQ1_ID0            P T_WJQ1_INTERIM

PK_T_WJQ1_ID                   P T_WJQ1

之前的實驗都是在單表情況下進行的線上重定義操作，但是，在實際的生產環境中，通常涉及到多表關係，例如外來鍵關係表下的重定義，那有該如何處理呢？接下來的案例，作者將介紹多表關係下的線上重定義。