使用DBMS_REDEFINITION在事務處理過程中將一個非分割槽錶轉換為分割槽表

使用DBMS_REDEFINITION在事務處理過程中將一個非分割槽錶轉換為分割槽表：
適用於11.2.0.2及以上版本

1、建立初始表：
SQL> create table unpar_table (
  a number,y number,
  name varchar2(100),
  date_used date);

為表新增約束和索引：
SQL> alter table unpar_table add(constraint unpar_table_pk primary key (a,y));
SQL> create index date_used_ind on unpar_table(date_used);

載入資料：
SQL> begin
    for i in 1 .. 1000
        loop
            for j in 1 .. 1000
                loop
                insert into unpar_table values ( i, j, dbms_random.random, sysdate-j );
                end loop;
        end loop;
end;
/
commit;


2、收集表統計資訊：
SQL> exec dbms_stats.gather_table_stats('wjw', 'unpar_table', cascade => TRUE);
SQL> select num_rows from user_tables where table_name = 'UNPAR_TABLE';
  NUM_ROWS
----------
   1000000

3、建立分割槽中間表：
SQL> create table par_table (
  a number,y number,
  name varchar2(100),
  date_used date)
   partition by range (date_used)
    (partition unpar_table_13 values less than (to_date('2013-07-01', 'yyyy-mm-dd')),
     partition unpar_table_14 values less than (to_date('2014-07-01', 'yyyy-mm-dd')),
     partition unpar_table_mx values less than (maxvalue));

4、執行重定義：

重定義之前先檢查表是否可以重定義：
exec dbms_redefinition.can_redef_table('wjw', 'unpar_table');
如果沒有任何錯誤，則接著執行下面步驟。

開始重定義：
SQL> begin
dbms_redefinition.start_redef_table(
uname => user,
orig_table => 'unpar_table',
int_table => 'par_table');
end;
/
上述過程實際上是使用CTAS建立了一個物化檢視：
SQL> select mview_name,container_name,build_mode from user_mviews;

MVIEW_NAME               CONTAINER_NAME              BUILD_MOD
------------------------------ ------------------------------ ---------
PAR_TABLE               PAR_TABLE              PREBUILT

模擬事務處理，在dbms_redef被啟用的時候往原表中插入1000條資料：
SQL> begin
    for i in 1001 .. 1010
        loop
            for j in 1001 .. 1100
                loop
                insert into unpar_table values ( i, j, dbms_random.random, sysdate-j );
                end loop;
        end loop;
end;
/
commit;

上述1000條資料變化記錄在了物化檢視日誌中，可以在MLOG$_UNPAR_TABLE表中檢視：
SQL> select count(*) from MLOG$_UNPAR_TABLE;

  COUNT(*)
----------
      1000

在索引建立之前使用中間表同步：
SQL> begin
dbms_redefinition.sync_interim_table
(uname=>'wjw',
orig_table=>'unpar_table',
int_table=>'par_table');
end;
/

給中間表新增約束和索引：
SQL> alter table par_table add (constraint par_table_pk2 primary key (a,y));
SQL> create index date_used_ind2 on par_table(date_used);

對目標表進行統計資訊收集：
SQL> exec dbms_stats.gather_table_stats('wjw', 'par_table', cascade => TRUE);

結束重定義：
SQL> begin
dbms_redefinition.finish_redef_table(
uname => 'wjw',
orig_table => 'unpar_table',
int_table => 'par_table');
end;
/


目前兩個表已經是同步：
SQL> select count(*) from par_table;
  COUNT(*)
----------
   1001000

SQL> select count(*) from unpar_table;
  COUNT(*)
----------
   1001000



確認表已經轉換為分割槽表：
SQL> select partitioned from user_tables where table_name = 'UNPAR_TABLE';

PAR
---
YES

SQL> select partition_name, num_rows from user_tab_partitions where table_name = 'UNPAR_TABLE';

PARTITION_NAME             NUM_ROWS
------------------------------ ----------
UNPAR_TABLE_13               271000
UNPAR_TABLE_14               365000
UNPAR_TABLE_MX               365000

刪除中間表：
SQL> drop table par_table cascade constraints;

重新命名約束和索引：
SQL> ALTER TABLE unpar_table RENAME CONSTRAINT par_table_pk2 TO unpar_table_pk;
SQL> ALTER INDEX date_used_ind2 RENAME TO date_used_ind;