利用在线重定义功能（Oracle9i以上版本）
DROP TABLE T PURGE ;DROP TABLE T_NEW PURGE ;
CREATE TABLE T (ID NUMBER PRIMARY KEY ,TIME DATE);
INSERT INTO T(SELECT ROWNUM,CREATED FROM DBA_OBJECTS);
COMMIT;
SQL> EXEC DBMS_REDEFINITION.can_redef_table(USER,'T',DBMS_REDEFINITION.cons_use_pk);PL/SQL procedure successfully completed
CREATE TABLE T_NEW (ID NUMBER ,TIME DATE )PARTITION BY RANGE (TIME) ( PARTITION P1 VALUES LESS THAN (TO_DATE('2005-1-1','YYYY-MM-DD')), PARTITION P2 VALUES LESS THAN (TO_DATE('2010-1-1','YYYY-MM-DD')), PARTITION P3 VALUES LESS THAN (TO_DATE('2014-1-1','YYYY-MM-DD')), PARTITION P4 VALUES LESS THAN (MAXVALUE) )
SQL> EXEC DBMS_REDEFINITION.start_redef_table(USER,'T','T_NEW','ID ID,TIME TIME',DBMS_REDEFINITION.cons_use_pk);PL/SQL procedure successfully completedSQL> EXEC DBMS_REDEFINITION.finish_redef_table('JGDM','T','T_NEW');-JGDM为用户PL/SQL procedure successfully completedSQL> SELECT COUNT(*) FROM T; COUNT(*)---------- 57384SQL> SELECT COUNT(*) FROM T PARTITION (P2); COUNT(*)---------- 51944SQL> SELECT COUNT(*) FROM T PARTITION (P3); COUNT(*)---------- 4325SQL> SELECT COUNT(*) FROM T PARTITION (P4); COUNT(*)---------- 1115SQL>
转载于:https://www.cnblogs.com/Uncho/p/4064996.html
