上篇中，我們簡單地介紹瞭如何使用Oracle線上重定義特性進行資料表Online的結構變動操作。本篇我們從一個較複雜的案例出發，討論複雜變化情況下如何進行Online Redefinition，以及dbms_redefinition包各個關鍵方法的作用。

3、一個分割槽表的重定義動作

我們定義一個資料表T。

SQL> create table t as select object_id, object_name, created from dba_objects;

Table created

SQL> desc t;

Name Type Nullable Default Comments

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

OBJECT_ID NUMBER Y

OBJECT_NAME VARCHAR2(128) Y

CREATED DATE Y

SQL> alter table t add constraint pk_t primary key (object_id);

Table altered

SQL> select count(*) from t;

COUNT(*)

----------

75192

期望的重定義目標有幾個：首先使用object_id進行分割槽、created欄位從date型別變為timestamp型別。另外object_name欄位改名為object_name_2。中間定義表如下：

（分割槽，created變型別，object_name欄位改名）

SQL> create table t_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 (10000),

9 partition p2 values less than (50000),

10 partition p3 values less than (maxvalue)

11 )

12 ;

Table created

首先，判斷是否可以進行重定義操作。

SQL> set serveroutput on;

SQL> exec dbms_redefinition.can_redef_table( 'SCOTT','T',options_flag => dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed

啟動重定義動作。

SQL> exec dbms_redefinition.start_redef_table('SCOTT','T','T_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

SQL> set timing on;

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

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

SQL> col query for a20;

SQL> select mview_name, container_name, query, REFRESH_METHOD from user_mviews;

MVIEW_NAME CONTAINER_NAME QUERY REFRESH_METHOD

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

T_INTERIM T_INTERIM select object_id obj FAST

ect_id, object_name

object_name_2, to_ti

mestamp(created) cre

ated from "SCOTT"."T

" "T"

Executed in 0.031 seconds

SQL> select master, log_table from user_mview_logs;

MASTER LOG_TABLE

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

T MLOG$_T

Executed in 0.016 seconds

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

此時，資料表資料情況如下。

--源資料表和中間資料表已經實現同步

SQL> select count(*) from t;

COUNT(*)

----------

75192

Executed in 0.016 seconds

SQL> select count(*) from t_interim;

COUNT(*)

----------

75192

Executed in 0.031 seconds

--沒有DML語句過程，物化檢視日誌尚空

SQL> select count(*) from mlog$_t;

COUNT(*)

----------

0

Executed in 0.015 seconds

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

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

ü 從源資料表中將資料載入到Interim中；

ü 建立物化檢視日誌；

如果在這個過程中，發生DML操作，也就是說在start過程和之後有DML操作，有新資料插入到其中。

SQL> select max(object_id) from t;

MAX(OBJECT_ID)

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

76847

Executed in 0 seconds

SQL> insert into t select object_id+76847, object_name, created from dba_objects;

75199 rows inserted

Executed in 7.297 seconds

SQL> select count(*) from t;

COUNT(*)

----------

150391

Executed in 0.016 seconds

中間表的資料內容保持不變，並且物化檢視日誌積累了需要重新整理的資料條目。

SQL> select count(*) from t_interim;

COUNT(*)

----------

75192

Executed in 0.016 seconds

SQL> select count(*) from mlog$_t;

COUNT(*)

----------

75199

Executed in 0.016 seconds

此時存在資料的不一致和不統一。Oracle推薦要求使用sysnc_interim_table方法將重定義過程中出現的變化資料重新整理。

SQL> exec dbms_redefinition.sync_interim_table('SCOTT','T','T_INTERIM');

PL/SQL procedure successfully completed

Executed in 195.937 seconds

重新整理7萬左右資料，使用了超過三分鐘時間。在這個過程中，我們可以看到重新整理物化檢視過程。

SQL> select * from v$mvrefresh;

SID SERIAL# CURRMVOWNER CURRMVNAME

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

47 13 SCOTT T_INTERIM

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

SQL> select name, LAST_REFRESH from user_mview_refresh_times;

NAME LAST_REFRESH

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

T_INTERIM 2013-9-10 9:07:01

SQL> select name, LAST_REFRESH from user_mview_refresh_times;

NAME LAST_REFRESH

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

T_INTERIM 2013-9-10 9:15:28

結束後，我們發現interim表和mlog$_t日誌表資料的變化。

SQL> select count(*) from t_interim;

COUNT(*)

----------

150391

Executed in 0.016 seconds

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

SQL> select count(*) from mlog$_t;

COUNT(*)

----------

0

Executed in 0.016 seconds

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

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

SQL> set serveroutput on;

SQL> declare

2 error_count number:=0;

3 begin

4 dbms_redefinition.copy_table_dependents(uname => 'SCOTT',orig_table => 'T',int_table => 'T_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 /

0

PL/SQL procedure successfully completed

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

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

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

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

ü 將源資料表和Interim資料表表名進行置換；

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

ü 釋放開在中間表上的鎖定；

SQL> exec dbms_redefinition.finish_redef_table('SCOTT','T','T_INTERIM');

PL/SQL procedure successfully completed

Executed in 1.953 seconds

SQL> select count(*) from mlog$_t;

select count(*) from mlog$_t

ORA-00942: 表或檢視不存在

檢查處理結果。

--按照原定計劃，資料表變化成功；

SQL> desc t;

Name Type Nullable Default Comments

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

OBJECT_ID NUMBER Y

OBJECT_NAME_2 VARCHAR2(128) Y

CREATED TIMESTAMP(6) Y

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

Executed in 2.719 seconds

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

SQL> select partition_name from user_tab_partitions where table_name='T';

PARTITION_NAME

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

P1

P2

P3

Executed in 0.062 seconds

SQL> select constraint_name, constraint_type from user_constraints where table_name='T';

CONSTRAINT_NAME CONSTRAINT_TYPE

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

PK_T P

Executed in 0.062 seconds

我們之前討論的都是單表情況下的處理，如果是涉及到多表關係，例如外來鍵關係表下的重定義，是怎麼處理呢？

Oracle Online Redefinition線上重定義（中）

相關文章