使用dbms_redefinition包線上把大資料量非分割槽錶轉換為分割槽表

本實驗是線上使用dbms_redefinition包把大資料量非分割槽錶轉換為分割槽表。它適用於
Oracle Database - Enterprise Edition - Version 11.2.0.2.0 以及以後的任何版本.

本文使用DBMS_REDEFINITION包提供了大量詳細的方法把普通表線上的轉換為分割槽表。

下面是詳細的操作步驟:

1. 建立一個臨時的分割槽表，表結構與要轉換的非分割槽表結構相同.
2. 執行 DBMS_REDEFINITION.can_redef_table...
3.  執行 DBMS_REDEFINITION.start_redef_table...
4.  執行 DBMS_REDEFINITION.sync_interim_table...
5.  執行 DBMS_REDEFINITION.finish_redef_table...
6. 刪除臨時的分割槽表 Drop the interim table.

詳細的操作步驟：

本實驗在 11.2.0.3平臺下做的。

-- 建立非分割槽表，並初始化資料.

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));

-- 載入資料 1,000,000 行

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;

PL/SQL procedure successfully completed.
Elapsed: 00:01:56.90

Commit complete.
--收集統計資訊
SQL> EXEC DBMS_STATS.gather_table_stats(user, 'unpar_table', cascade => TRUE);

SQL>SELECT   num_rows FROM user_tables WHERE table_name = 'UNPAR_TABLE';
NUM_ROWS
----------
   1000000

Elapsed: 00:00:00.01
--建立分割槽表
SQL> CREATE TABLE par_table (
    a NUMBER, y number,
    name VARCHAR2(100),date_used DATE)
   PARTITION BY RANGE (date_used)
    (PARTITION unpar_table_12 VALUES LESS THAN (TO_DATE('10/07/2012', 'DD/MM/YYYY')),
     PARTITION unpar_table_15 VALUES LESS THAN (TO_DATE('15/07/2012', 'DD/MM/YYYY')),
     PARTITION unpar_table_MX VALUES LESS THAN (MAXVALUE));

SQL> EXEC Dbms_Redefinition.can_redef_table(USER, 'unpar_table');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07

--  這個儲存過程包 (DBMS_REDEFINITION.start_redef_table) 會建立一個物化檢視.

SQL> BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => USER,
orig_table => 'unpar_table',
int_table => 'par_table');
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.69

SQL> select mview_name,container_name, build_mode from user_mviews;
MVIEW_NAME                     CONTAINER_NAME                 BUILD_MOD
------------------------------ ------------------------------ ---------
PAR_TABLE                      PAR_TABLE                      PREBUILT

Elapsed: 00:00:00.13

-- 當 DBMS_REDEF 為活動狀態是，我們向非分割槽表'unpar_table'插入1000資料.
-- 我們會使用到 DBMS_REDEFINITION.start_redef_table建立的物化檢視記錄更新的資訊.
-- 檢查物化檢視 MLOG$_

確認是否有更新的記錄.

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;

Elapsed: 00:00:00.24

SQL> select count(*) from MLOG$_UNPAR_TABLE;

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

-- 執行 dbms_redefinition.sync_interim_table基於物化檢視 FAST REFRESH
-- 線上更新資料. 這將清空物化檢視中被應用的每一條記錄.
-- 在我們執行Finish_REDEF_TABLE儲存過程包之前，我們可以執行多次.

SQL> BEGIN
dbms_redefinition.sync_interim_table(
uname => USER,
orig_table => 'unpar_table',
int_table => 'par_table');
END;

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.01

SQL> ALTER TABLE par_table ADD (CONSTRAINT par_table_pk2 PRIMARY KEY (a,y));

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'par_table', cascade => TRUE);

-- 執行儲存過程Finish_redef_table 包，將交換表名，臨時分割槽表名將變為原非分割槽表名.
-- 執行完這步驟後, 原非分割槽表將基於臨時分割槽表重新定義表屬性和資料.
-- 在此期間原非分割槽表將會短暫的鎖住表.

SQL>BEGIN
dbms_redefinition.finish_redef_table(
uname => USER,
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

-- 從資料字典表中我們來確認非分割槽表 "UNPAR_TABLE"的結構變為了分割槽表.

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_12                     980000
UNPAR_TABLE_15                       5000
UNPAR_TABLE_MX                      16000

-- 此時我們完成了線上非分割槽表的轉換，我們可以刪除臨時分割槽表了.

SQL> drop TABLE par_table cascade constraints;

注意先前的實驗版本是在 11.2.0.3完成的.

下面的一個測試是在 11.1.0.7 下，執行 sync_interim_table儲存包執行的時間，表的資料量為 (1,000,000 ) .

example:

11.1.0.7
SYNC of 1000 rows -- Elapsed: 00:01:36.30 (1.5 minutes)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12798004/viewspace-1571474/，如需轉載，請註明出處，否則將追究法律責任。