使用DBMS_REDEFINITION在线切换普通表到分区表

电玩女神 2022-08-23 12:55 68阅读 0赞

随着数据库数据量的不断增长，有些表需要由普通的堆表转换为分区表的模式。有几种不同的方法来对此进行操作，诸如导出表数据，然后创建分区表再导入数据到分区表；使用EXCHANGE PARTITION方式来转换为分区表以及使用DBMS\_REDEFINITION来在线重定义分区表。本文描述的是使用DBMS\_REDEFINITION来实现，下面是具体的操作示例。

有关具体的dbms\_redefinition在线重定义表的原理及步骤可参考：[基于 dbms\_redefinition 在线重定义表  
][dbms_redefinition _]      有关分区表的描述请参考：[Oracle 分区表][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 ROW
    BEGIN
       :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_info
    TABLE_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 ROW
    BEGIN
       :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_info
    
    TABLE_NAME                     INDEX_NAME                     CL_NAM                         STATUS   PAR
    ------------------------------ ------------------------------ ------------------------------ -------- ---
    BIG_TABLE                      BIG_TABLE_PK2                  ID                             VALID    NO
    BIG_TABLE                      BITA_LOOK_FK_I2                LOOKUP_ID                      N/A      YES
    BIG_TABLE                      BITA_CREATED_DATE_I2           CREATED_DATE                   N/A      YES
    BIG_TABLE2                     BITA_CREATED_DATE_I            CREATED_DATE                   VALID    NO
    BIG_TABLE2                     BIG_TABLE_PK                   ID                             VALID    NO
    BIG_TABLE2                     BITA_LOOK_FK_I                 LOOKUP_ID                      VALID    NO
    LOOKUP                         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',     333793
    BIG_TABLE                 BIG_TABLE_2013                 TO_DATE(' 2014-01-01 00:00:00',     332025
    BIG_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 off
    SQL> variable num_errors number
    
    BEGIN
    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
     (uname=>'LESHAMI',
     orig_table=>'BIG_TABLE',
     int_table=>'BIG_TABLE2',
     num_errors=>:num_errors);
    END;
    /
    
    SQL> print num_errors
    
    NUM_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    NO
    BIG_TABLE                 BIG_TABLE_PK                   ID                        1 VALID    NO
    BIG_TABLE                 BITA_CREATED_DATE_I            CREATED_DATE              1 VALID    NO
    BIG_TABLE                 BITA_LOOK_FK_I                 LOOKUP_ID                 1 VALID    NO
    BIG_TABLE2                TMP$$_BIG_TABLE_PK0            ID                        1 VALID    NO
    BIG_TABLE2                TMP$$_BITA_CREATED_DATE_I0     CREATED_DATE              1 VALID    NO
    BIG_TABLE2                TMP$$_BITA_LOOK_FK_I0          LOOKUP_ID                 1 VALID    NO

5 、参考：[http://www.oracle-base.com/articles/misc/partitioning-an-existing-table.php][http_www.oracle-base.com_articles_misc_partitioning-an-existing-table.php]

[dbms_redefinition _]: http://blog.csdn.net/leshami/article/details/11760417
[Oracle]: http://blog.csdn.net/leshami/article/details/5925572
[http_www.oracle-base.com_articles_misc_partitioning-an-existing-table.php]: http://www.oracle-base.com/articles/misc/partitioning-an-existing-table.php