使用DBMS_REDEFINITION線上切換普通表到分區表

      隨著資料庫資料量的不斷增長，有些表需要由普通的堆錶轉換為分區表的模式。有幾種不同的方法來對此進行操作，諸如匯出表資料，然後建立分區表再匯入資料到分區表；使用EXCHANGE PARTITION方式來轉換為分區表以及使用DBMS_REDEFINITION來線上重定義分區表。本文描述的是使用DBMS_REDEFINITION來實現，下面是具體的操作樣本。

      有關具體的dbms_redefinition線上重定義表的原理及步驟可參考：基於 dbms_redefinition 線上重定義表
      有關分區表的描述請參考：Oracle 分區表

 

1、準備環境

--建立使用者SQL> create user leshami identified by xxx;SQL> grant dba to leshami;--建立示範需要用到的資料表空間SQL> create tablespace tbs_tmp datafile '/u02/database/SYBO2/oradata/tbs_tmp.dbf' size 10m autoextend on;SQL> alter user leshami default tablespace tbs_tmp;SQL> create tablespace tbs1 datafile '/u02/database/SYBO2/oradata/tbs1.dbf' size 10m autoextend on;SQL> create tablespace tbs2 datafile '/u02/database/SYBO2/oradata/tbs2.dbf' size 10m autoextend on;SQL> create tablespace tbs3 datafile '/u02/database/SYBO2/oradata/tbs3.dbf' size 10m autoextend on;SQL> conn leshami/xxx-- 建立一個lookup表CREATE TABLE lookup (  id            NUMBER(10),  description   VARCHAR2(50));--添加主鍵約束ALTER TABLE lookup ADD (  CONSTRAINT lookup_pk PRIMARY KEY (id));--插入資料INSERT INTO lookup (id, description) VALUES (1, 'ONE');INSERT INTO lookup (id, description) VALUES (2, 'TWO');INSERT INTO lookup (id, description) VALUES (3, 'THREE');COMMIT;--建立一個用於切換到分區的大表CREATE TABLE big_table (  id            NUMBER(10),  created_date  DATE,  lookup_id     NUMBER(10),  data          VARCHAR2(50));--填充資料到大表DECLARE  l_lookup_id    lookup.id%TYPE;  l_create_date  DATE;BEGIN  FOR i IN 1 .. 1000000 LOOP    IF MOD(i, 3) = 0 THEN      l_create_date := ADD_MONTHS(SYSDATE, -24);      l_lookup_id   := 2;    ELSIF MOD(i, 2) = 0 THEN      l_create_date := ADD_MONTHS(SYSDATE, -12);      l_lookup_id   := 1;    ELSE      l_create_date := SYSDATE;      l_lookup_id   := 3;    END IF;        INSERT INTO big_table (id, created_date, lookup_id, data)    VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);  END LOOP;  COMMIT;END;/--為大表添加主、外鍵約束，索引，以及添加觸發器等.ALTER TABLE big_table ADD (  CONSTRAINT big_table_pk PRIMARY KEY (id));CREATE INDEX bita_created_date_i ON big_table(created_date);CREATE INDEX bita_look_fk_i ON big_table(lookup_id);ALTER TABLE big_table ADD (  CONSTRAINT bita_look_fk  FOREIGN KEY (lookup_id)  REFERENCES lookup(id));CREATE OR REPLACE TRIGGER tr_bf_big_table   BEFORE UPDATE OF created_date   ON big_table   FOR EACH ROWBEGIN   :new.created_date := TO_CHAR (SYSDATE, 'yyyymmdd hh24:mi:ss');END tr_bf_big_table;/--收集統計資訊EXEC DBMS_STATS.gather_table_stats('LESHAMI', 'LOOKUP', cascade => TRUE);EXEC DBMS_STATS.gather_table_stats('LESHAMI', 'BIG_TABLE', cascade => TRUE);--表上的索引相關資訊SQL> @idx_infoTABLE_NAME                INDEX_NAME                     CL_NAM                  STATUS   PAR ------------------------- ------------------------------ --------------------    -------- --- LOOKUP                    LOOKUP_PK                      ID                      VALID    NO  BIG_TABLE                 BIG_TABLE_PK                   ID                      VALID    NO BIG_TABLE                 BITA_CREATED_DATE_I            CREATED_DATE            VALID    NO BIG_TABLE                 BITA_LOOK_FK_I                 LOOKUP_ID               VALID    NO--建立一個中間表(分區表)CREATE TABLE big_table2 (  id            NUMBER(10),  created_date  DATE,  lookup_id     NUMBER(10),  data          VARCHAR2(50))PARTITION BY RANGE (created_date)(PARTITION big_table_2012 VALUES LESS THAN (TO_DATE('01/01/2013', 'DD/MM/YYYY')) tablespace tbs1, PARTITION big_table_2013 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')) tablespace tbs2, PARTITION big_table_2014 VALUES LESS THAN (MAXVALUE) tablespace tbs3);

2、切換普通表到分區表

----校正表能否被重定義 EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('LESHAMI', 'BIG_TABLE');--開始重定義BEGIN DBMS_REDEFINITION.start_redef_table(   uname      => 'LESHAMI',           orig_table => 'BIG_TABLE',   int_table  => 'BIG_TABLE2');END;/--可選步驟，用於同步重定義表與暫存資料表  BEGIN  dbms_redefinition.sync_interim_table(    uname      => 'LESHAMI',            orig_table => 'BIG_TABLE',    int_table  => 'BIG_TABLE2');END;/--為中間表添加相應的索引，約束等ALTER TABLE big_table2 ADD (  CONSTRAINT big_table_pk2 PRIMARY KEY (id));CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;ALTER TABLE big_table2 ADD (  CONSTRAINT bita_look_fk2  FOREIGN KEY (lookup_id)  REFERENCES lookup(id));--為中間表添加觸發器CREATE OR REPLACE TRIGGER tr_bf_big_table2   BEFORE UPDATE OF created_date   ON big_table2   FOR EACH ROWBEGIN   :new.created_date := TO_CHAR (SYSDATE, 'yyyymmdd hh24:mi:ss');END tr_bf_big_table2;/-- Gather statistics on the new table.EXEC DBMS_STATS.gather_table_stats('LESHAMI', 'BIG_TABLE2', cascade => TRUE);--完成線上重定義BEGIN  dbms_redefinition.finish_redef_table(      --Author : Leshami    uname      => 'LESHAMI',                 --Blog   : http://blog.csdn.net/leshmai      orig_table => 'BIG_TABLE',    int_table  => 'BIG_TABLE2');END;/--可以看到新的表BIG_TABLE上的索引已經成為分區索引，如最後一列所示SQL> @idx_infoTABLE_NAME                     INDEX_NAME                     CL_NAM                         STATUS   PAR------------------------------ ------------------------------ ------------------------------ -------- ---BIG_TABLE                      BIG_TABLE_PK2                  ID                             VALID    NOBIG_TABLE                      BITA_LOOK_FK_I2                LOOKUP_ID                      N/A      YESBIG_TABLE                      BITA_CREATED_DATE_I2           CREATED_DATE                   N/A      YESBIG_TABLE2                     BITA_CREATED_DATE_I            CREATED_DATE                   VALID    NOBIG_TABLE2                     BIG_TABLE_PK                   ID                             VALID    NOBIG_TABLE2                     BITA_LOOK_FK_I                 LOOKUP_ID                      VALID    NOLOOKUP                         LOOKUP_PK                      ID                             VALID    NO--校正分區表SQL> select table_name, partition_name,high_value,num_rows from user_tab_partitions where table_name='BIG_TABLE';TABLE_NAME                PARTITION_NAME                 HIGH_VALUE                        NUM_ROWS------------------------- ------------------------------ ------------------------------- ----------BIG_TABLE                 BIG_TABLE_2012                 TO_DATE(' 2013-01-01 00:00:00',     333793BIG_TABLE                 BIG_TABLE_2013                 TO_DATE(' 2014-01-01 00:00:00',     332025BIG_TABLE                 BIG_TABLE_2014                 MAXVALUE                            333128

3、切換後的收尾工作

--移除中間表DROP TABLE big_table2;--根據需要可以重新命名索引及約束名ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;ALTER INDEX big_table_pk2 RENAME TO big_table_pk;ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;

4、其它

--在使用DBMS_REDEFINITION期間，如果我們使用COPY_TABLE_DEPENDENTS過程，其索引依舊為非分區索引--需要刪除原來的索引之後，然後重新建立為新的本地分區索引/*SQL> set timing offSQL> variable num_errors numberBEGINDBMS_REDEFINITION.COPY_TABLE_DEPENDENTS (uname=>'LESHAMI', orig_table=>'BIG_TABLE', int_table=>'BIG_TABLE2', num_errors=>:num_errors);END;/SQL> print num_errorsNUM_ERRORS----------         0   */         ----------------------------------------------------------如下為使用COPY_TABLE_DEPENDENTS依賴對象之後表BIG_TABLE2多出了幾個TMP$$開頭的索引，但是索引依舊為普通索引，而非分區索引  SQL> @idx_info2 TABLE_NAME                INDEX_NAME                     CL_NAM               CL_POS STATUS   PAR------------------------- ------------------------------ -------------------- ------ -------- ---LOOKUP                    LOOKUP_PK                      ID                        1 VALID    NOBIG_TABLE                 BIG_TABLE_PK                   ID                        1 VALID    NOBIG_TABLE                 BITA_CREATED_DATE_I            CREATED_DATE              1 VALID    NOBIG_TABLE                 BITA_LOOK_FK_I                 LOOKUP_ID                 1 VALID    NOBIG_TABLE2                TMP$$_BIG_TABLE_PK0            ID                        1 VALID    NOBIG_TABLE2                TMP$$_BITA_CREATED_DATE_I0     CREATED_DATE              1 VALID    NOBIG_TABLE2                TMP$$_BITA_LOOK_FK_I0          LOOKUP_ID                 1 VALID    NO

 5 、參考：http://www.oracle-base.com/articles/misc/partitioning-an-existing-table.php