使用DBMS_REDEFINITION在线重定义分区表

本文主要是介绍使用DBMS_REDEFINITION在线重定义分区表，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

创建一个非分区表,注意两个表的表结构和字段类型一致.

SQL> CREATE TABLE tab_unpart
2 (
3 col1 varchar2(30),
4 col2 DATE
5 );TABLE created.

SQL> INSERT INTO tab_unpart(col1, col2) SELECT -1, sysdate-365 FROM dual;1 ROW created.SQL> INSERT INTO tab_unpart(col1, col2) SELECT 0, sysdate FROM dual;1 ROW created.SQL> INSERT INTO tab_unpart(col1, col2) SELECT +1, sysdate+360 FROM dual;1 ROW created.SQL> commit;Commit complete.SQL> SELECT col1,to_char(col2,'yyyy-mm-dd') FROM tab_unpart;COL TO_CHAR(CO
--- ----------
-1  2011-01-05
0   2012-01-05
1   2012-12-30

创建一个分区表

SQL> CREATE TABLE tab_part2  (3  col1 varchar2(30),4  col2 DATE5  )6  partition BY range(col2)7  (8  partition tab_part_2011 VALUES less than (to_date('2011-01-01','yyyy-mm-dd')),9  partition tab_part_2012 VALUES less than (to_date('2012-01-01','yyyy-mm-dd')),10  partition tab_part_2013 VALUES less than (to_date('2013-01-01','yyyy-mm-dd')),11  );TABLE created.SQL> @partition_NAME            OBJECT COLUMN_NAME PARTITIONING_TYPE    STATUS  PARTITION_NAME
--------------- ------ ----------- -------------------- ------- ---------------
TAB_PART        TABLE  COL2        RANGE                VALID   TAB_PART_2013
TAB_PART        TABLE  COL2        RANGE                VALID   TAB_PART_2011
TAB_PART        TABLE  COL2        RANGE                VALID   TAB_PART_2012

验证表TAB_UNPART是否可以在线重定义,如果不可以会给出建议

SQL> EXEC dbms_redefinition.can_redef_table('TEST','TAB_UNPART',1);
BEGIN dbms_redefinition.can_redef_table('TEST','TAB_UNPART',1); END;*
ERROR at line 1:
ORA-12089: cannot online redefine TABLE "TEST"."TAB_UNPART" WITH no PRIMARY KEY
ORA-06512: at "SYS.DBMS_REDEFINITION", line 139
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1782
ORA-06512: at line 1

给两张表添加主键

SQL> ALTER TABLE TAB_UNPART ADD CONSTRAINT pk_unpart_col1 PRIMARY KEY (COL1);TABLE altered.SQL> ALTER TABLE TAB_PART ADD CONSTRAINT pk_part_col1 PRIMARY KEY (COL1);TABLE altered.

再次验证

SQL> EXEC dbms_redefinition.can_redef_table('TEST','TAB_UNPART',1);PL/SQL PROCEDURE successfully completed.

使用DBMS_REDEFINITION将非分区表转为分区表

SQL> @partition_NAME            OBJECT COLUMN_NAME PARTITIONING_TYPE    STATUS  PARTITION_NAME
--------------- ------ ----------- -------------------- ------- ---------------
TAB_PART        TABLE  COL2        RANGE                VALID   TAB_PART_2013
TAB_PART        TABLE  COL2        RANGE                VALID   TAB_PART_2011
TAB_PART        TABLE  COL2        RANGE                VALID   TAB_PART_2012SQL> BEGIN2  DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST','TAB_UNPART',2);3  DBMS_REDEFINITION.START_REDEF_TABLE('TEST','TAB_UNPART','TAB_PART',NULL,2);4  DBMS_REDEFINITION.FINISH_REDEF_TABLE('TEST','TAB_UNPART','TAB_PART');5  END;6  /PL/SQL PROCEDURE successfully completed.SQL> @partition_NAME            OBJECT COLUMN_NAME PARTITIONING_TYPE    STATUS  PARTITION_NAME
--------------- ------ ----------- -------------------- ------- ---------------
TAB_UNPART      TABLE  COL2        RANGE                VALID   TAB_PART_2013
TAB_UNPART      TABLE  COL2        RANGE                VALID   TAB_PART_2011
TAB_UNPART      TABLE  COL2        RANGE                VALID   TAB_PART_2012

SQL> SELECT * FROM tab_unpart partition (TAB_PART_2012);COL COL2
--- ------------
-1  05-JAN-11SQL> SELECT * FROM tab_unpart partition (TAB_PART_2011);no ROWS selectedSQL> SELECT * FROM tab_unpart partition (TAB_PART_2013);COL COL2
--- ------------
0   05-JAN-12
1   30-DEC-12

这篇关于使用DBMS_REDEFINITION在线重定义分区表的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---