使用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 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

秒客网

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

相关文章