利用在线重定义功能（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 completed

SQL> EXEC DBMS_REDEFINITION.finish_redef_table(‘JGDM‘,‘T‘,‘T_NEW‘);-JGDM为用户

PL/SQL procedure successfully completed

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
57384

SQL> SELECT COUNT(*) FROM T PARTITION (P2);

COUNT(*)
----------
51944

SQL> SELECT COUNT(*) FROM T PARTITION (P3);

COUNT(*)
----------
4325

SQL> SELECT COUNT(*) FROM T PARTITION (P4);

COUNT(*)
----------
1115

SQL>

Oralce重定义分区