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

转自 http://blog.csdn.net/leshami/article/details/30041813  

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

      有关具体的dbms_redefinition在线重定义表的原理及步骤可参考：基于 dbms_redefinition 在线重定义表 
      有关分区表的描述请参考：Oracle 分区表

 

1、准备环境

[sql] view plaincopyprint?在CODE上查看代码片派生到我的代码片
  1. --创建用户  
  2. SQL> create user leshami identified by xxx;  
  3.   
  4. SQL> grant dba to leshami;  
  5.   
  6. --创建演示需要用到的表空间  
  7. SQL> create tablespace tbs_tmp datafile '/u02/database/SYBO2/oradata/tbs_tmp.dbf' size 10m autoextend on;  
  8.   
  9. SQL> alter user leshami default tablespace tbs_tmp;  
  10.   
  11. SQL> create tablespace tbs1 datafile '/u02/database/SYBO2/oradata/tbs1.dbf' size 10m autoextend on;  
  12.   
  13. SQL> create tablespace tbs2 datafile '/u02/database/SYBO2/oradata/tbs2.dbf' size 10m autoextend on;  
  14.   
  15. SQL> create tablespace tbs3 datafile '/u02/database/SYBO2/oradata/tbs3.dbf' size 10m autoextend on;  
  16.   
  17. SQL> conn leshami/xxx  
  18.   
  19. -- 创建一个lookup表  
  20. CREATE TABLE lookup (  
  21.   id            NUMBER(10),  
  22.   description   VARCHAR2(50)  
  23. );  
  24.   
  25. --添加主键约束  
  26. ALTER TABLE lookup ADD (  
  27.   CONSTRAINT lookup_pk PRIMARY KEY (id)  
  28. );  
  29.   
  30. --插入数据  
  31. INSERT INTO lookup (id, description) VALUES (1, 'ONE');  
  32. INSERT INTO lookup (id, description) VALUES (2, 'TWO');  
  33. INSERT INTO lookup (id, description) VALUES (3, 'THREE');  
  34. COMMIT;  
  35.   
  36. --创建一个用于切换到分区的大表  
  37. CREATE TABLE big_table (  
  38.   id            NUMBER(10),  
  39.   created_date  DATE,  
  40.   lookup_id     NUMBER(10),  
  41.   data          VARCHAR2(50)  
  42. );  
  43.   
  44. --填充数据到大表  
  45. DECLARE  
  46.   l_lookup_id    lookup.id%TYPE;  
  47.   l_create_date  DATE;  
  48. BEGIN  
  49.   FOR i IN 1 .. 1000000 LOOP  
  50.     IF MOD(i, 3) = 0 THEN  
  51.       l_create_date := ADD_MONTHS(SYSDATE, -24);  
  52.       l_lookup_id   := 2;  
  53.     ELSIF MOD(i, 2) = 0 THEN  
  54.       l_create_date := ADD_MONTHS(SYSDATE, -12);  
  55.       l_lookup_id   := 1;  
  56.     ELSE  
  57.       l_create_date := SYSDATE;  
  58.       l_lookup_id   := 3;  
  59.     END IF;  
  60.       
  61.     INSERT INTO big_table (id, created_date, lookup_id, data)  
  62.     VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);  
  63.   END LOOP;  
  64.   COMMIT;  
  65. END;  
  66. /  
  67.   
  68. --为大表添加主、外键约束，索引，以及添加触发器等.  
  69. ALTER TABLE big_table ADD (  
  70.   CONSTRAINT big_table_pk PRIMARY KEY (id)  
  71. );  
  72.   
  73. CREATE INDEX bita_created_date_i ON big_table(created_date);  
  74.   
  75. CREATE INDEX bita_look_fk_i ON big_table(lookup_id);  
  76.   
  77. ALTER TABLE big_table ADD (  
  78.   CONSTRAINT bita_look_fk  
  79.   FOREIGN KEY (lookup_id)  
  80.   REFERENCES lookup(id)  
  81. );  
  82.   
  83. CREATE OR REPLACE TRIGGER tr_bf_big_table  
  84.    BEFORE UPDATE OF created_date  
  85.    ON big_table  
  86.    FOR EACH ROW  
  87. BEGIN  
  88.    :new.created_date := TO_CHAR (SYSDATE, 'yyyymmdd hh24:mi:ss');  
  89. END tr_bf_big_table;  
  90. /  
  91.   
  92. --收集统计信息  
  93. EXEC DBMS_STATS.gather_table_stats('LESHAMI', 'LOOKUP', cascade => TRUE);  
  94. EXEC DBMS_STATS.gather_table_stats('LESHAMI', 'BIG_TABLE', cascade => TRUE);  
  95.   
  96. --表上的索引相关信息  
  97. SQL> @idx_info  
  98. TABLE_NAME                INDEX_NAME                     CL_NAM                  STATUS   PAR   
  99. ------------------------- ------------------------------ --------------------    -------- ---   
  100. LOOKUP                    LOOKUP_PK                      ID                      VALID    NO    
  101. BIG_TABLE                 BIG_TABLE_PK                   ID                      VALID    NO   
  102. BIG_TABLE                 BITA_CREATED_DATE_I            CREATED_DATE            VALID    NO   
  103. BIG_TABLE                 BITA_LOOK_FK_I                 LOOKUP_ID               VALID    NO  
  104.   
  105. --创建一个中间表(分区表)  
  106. CREATE TABLE big_table2 (  
  107.   id            NUMBER(10),  
  108.   created_date  DATE,  
  109.   lookup_id     NUMBER(10),  
  110.   data          VARCHAR2(50)  
  111. )  
  112. PARTITION BY RANGE (created_date)  
  113. (PARTITION big_table_2012 VALUES LESS THAN (TO_DATE('01/01/2013', 'DD/MM/YYYY')) tablespace tbs1,  
  114.  PARTITION big_table_2013 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')) tablespace tbs2,  
  115.  PARTITION big_table_2014 VALUES LESS THAN (MAXVALUE) tablespace tbs3);  

2、切换普通表到分区表

[sql] view plaincopyprint?在CODE上查看代码片派生到我的代码片
  1. ----校验表能否被重定义   
  2. EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('LESHAMI', 'BIG_TABLE');  
  3.   
  4. --开始重定义  
  5. BEGIN  
  6.  DBMS_REDEFINITION.start_redef_table(  
  7.    uname      => 'LESHAMI',          
  8.    orig_table => 'BIG_TABLE',  
  9.    int_table  => 'BIG_TABLE2');  
  10. END;  
  11. /  
  12.   
  13. --可选步骤，用于同步重定义表与临时表    
  14. BEGIN  
  15.   dbms_redefinition.sync_interim_table(  
  16.     uname      => 'LESHAMI',          
  17.     orig_table => 'BIG_TABLE',  
  18.     int_table  => 'BIG_TABLE2');  
  19. END;  
  20. /  
  21.   
  22. --为中间表添加相应的索引，约束等  
  23. ALTER TABLE big_table2 ADD (  
  24.   CONSTRAINT big_table_pk2 PRIMARY KEY (id)  
  25. );  
  26.   
  27. CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;  
  28.   
  29. CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;  
  30.   
  31. ALTER TABLE big_table2 ADD (  
  32.   CONSTRAINT bita_look_fk2  
  33.   FOREIGN KEY (lookup_id)  
  34.   REFERENCES lookup(id)  
  35. );  
  36.   
  37. --为中间表添加触发器  
  38. CREATE OR REPLACE TRIGGER tr_bf_big_table2  
  39.    BEFORE UPDATE OF created_date  
  40.    ON big_table2  
  41.    FOR EACH ROW  
  42. BEGIN  
  43.    :new.created_date := TO_CHAR (SYSDATE, 'yyyymmdd hh24:mi:ss');  
  44. END tr_bf_big_table2;  
  45. /  
  46.   
  47. -- Gather statistics on the new table.  
  48. EXEC DBMS_STATS.gather_table_stats('LESHAMI', 'BIG_TABLE2', cascade => TRUE);  
  49.   
  50. --完成在线重定义  
  51. BEGIN  
  52.   dbms_redefinition.finish_redef_table(      --Author : Leshami  
  53.     uname      => 'LESHAMI',                 --Blog   : http://blog.csdn.net/leshmai    
  54.     orig_table => 'BIG_TABLE',  
  55.     int_table  => 'BIG_TABLE2');  
  56. END;  
  57. /  
  58.   
  59. --可以看到新的表BIG_TABLE上的索引已经成为分区索引，如最后一列所示  
  60. SQL> @idx_info  
  61.   
  62. TABLE_NAME                     INDEX_NAME                     CL_NAM                         STATUS   PAR  
  63. ------------------------------ ------------------------------ ------------------------------ -------- ---  
  64. BIG_TABLE                      BIG_TABLE_PK2                  ID                             VALID    NO  
  65. BIG_TABLE                      BITA_LOOK_FK_I2                LOOKUP_ID                      N/A      YES  
  66. BIG_TABLE                      BITA_CREATED_DATE_I2           CREATED_DATE                   N/A      YES  
  67. BIG_TABLE2                     BITA_CREATED_DATE_I            CREATED_DATE                   VALID    NO  
  68. BIG_TABLE2                     BIG_TABLE_PK                   ID                             VALID    NO  
  69. BIG_TABLE2                     BITA_LOOK_FK_I                 LOOKUP_ID                      VALID    NO  
  70. LOOKUP                         LOOKUP_PK                      ID                             VALID    NO  
  71.   
  72. --校验分区表  
  73. SQL> select table_name, partition_name,high_value,num_rows from user_tab_partitions where table_name='BIG_TABLE';  
  74.   
  75. TABLE_NAME                PARTITION_NAME                 HIGH_VALUE                        NUM_ROWS  
  76. ------------------------- ------------------------------ ------------------------------- ----------  
  77. BIG_TABLE                 BIG_TABLE_2012                 TO_DATE(' 2013-01-01 00:00:00',     333793  
  78. BIG_TABLE                 BIG_TABLE_2013                 TO_DATE(' 2014-01-01 00:00:00',     332025  
  79. BIG_TABLE                 BIG_TABLE_2014                 MAXVALUE                            333128  

3、切换后的收尾工作

[sql] view plaincopyprint?在CODE上查看代码片派生到我的代码片
  1. --移除中间表  
  2. DROP TABLE big_table2;  
  3.   
  4. --根据需要可以重命名索引及约束名  
  5. ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;  
  6. ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;  
  7. ALTER INDEX big_table_pk2 RENAME TO big_table_pk;  
  8. ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;  
  9. ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;  

4、其它

[sql] view plaincopyprint?在CODE上查看代码片派生到我的代码片
  1. --在使用DBMS_REDEFINITION期间，如果我们使用COPY_TABLE_DEPENDENTS过程，其索引依旧为非分区索引  
  2. --需要删除原来的索引之后，然后重新创建为新的本地分区索引  
  3. /*SQL> set timing off  
  4. SQL> variable num_errors number  
  5.   
  6. BEGIN  
  7. DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS  
  8.  (uname=>'LESHAMI',  
  9.  orig_table=>'BIG_TABLE',  
  10.  int_table=>'BIG_TABLE2',  
  11.  num_errors=>:num_errors);  
  12. END;  
  13. /  
  14.   
  15. SQL> print num_errors  
  16.   
  17. NUM_ERRORS  
  18. ----------  
  19.          0   */  
  20.            
  21. --------------------------------------------------------  
  22. --如下为使用COPY_TABLE_DEPENDENTS依赖对象之后表BIG_TABLE2多出了几个TMP$$开头的索引，但是索引依旧为普通索引，而非分区索引    
  23. SQL> @idx_info2   
  24. TABLE_NAME                INDEX_NAME                     CL_NAM               CL_POS STATUS   PAR  
  25. ------------------------- ------------------------------ -------------------- ------ -------- ---  
  26. LOOKUP                    LOOKUP_PK                      ID                        1 VALID    NO  
  27. BIG_TABLE                 BIG_TABLE_PK                   ID                        1 VALID    NO  
  28. BIG_TABLE                 BITA_CREATED_DATE_I            CREATED_DATE              1 VALID    NO  
  29. BIG_TABLE                 BITA_LOOK_FK_I                 LOOKUP_ID                 1 VALID    NO  
  30. BIG_TABLE2                TMP$$_BIG_TABLE_PK0            ID                        1 VALID    NO  
  31. BIG_TABLE2                TMP$$_BITA_CREATED_DATE_I0     CREATED_DATE              1 VALID    NO  
  32. 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

posted @ 2014-07-09 00:33  princessd8251  阅读(166)  评论(0)    收藏  举报