近期关于表分区进行一定学习和参考网上的部分资料的基础上，对原表有数据情况时的三种分区方法进行部分补充：

--方法一，利用原表重建分区表（本测试是通过system用户登录并执行的）drop table t
drop table t_old

CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
INSERT INTO T
SELECT ROWNUM, SYSDATE - ROWNUM FROM DBA_OBJECTS WHERE ROWNUM <= 2000;
COMMIT;

CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)
(PARTITION P1 VALUES LESS THAN (TO_DATE('2008-1-1', 'YYYY-MM-DD')),
PARTITION P2 VALUES LESS THAN (TO_DATE('2009-1-1', 'YYYY-MM-DD')),
PARTITION P3 VALUES LESS THAN (TO_DATE('2010-1-1', 'YYYY-MM-DD')),
PARTITION P4 VALUES LESS THAN (MAXVALUE))
AS SELECT ID, TIME FROM T;

RENAME T TO T_OLD;
RENAME T_NEW TO T;

SELECT COUNT(*) FROM T;
SELECT COUNT(*) FROM T PARTITION (P1);
SELECT COUNT(*) FROM T PARTITION (P2);
SELECT COUNT(*) FROM T PARTITION (P3);
SELECT COUNT(*) FROM T PARTITION (P4);

SELECT * FROM T;

注意点：rename名利只能在同一个用户才能执行成功。如果写成如下则会报错：
RENAME system.T TO system.T_OLD;

或system用户身份企图修改别的用户，也同样会报错：
RENAME rep.T TO rep.T_OLD;

--方法二：使用交换分区的方法（本测试是通过system用户登录并执行的）

Drop table t;
Drop table t_new;
Drop table t_old;

CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
INSERT INTO T
SELECT ROWNUM, SYSDATE - ROWNUM FROM DBA_OBJECTS WHERE ROWNUM <= 2000;
COMMIT;

CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
(PARTITION P1 VALUES LESS THAN (TO_DATE('2012-1-1', 'YYYY-MM-DD')),
PARTITION P2 VALUES LESS THAN (MAXVALUE));

ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T;

RENAME T TO T_OLD;
RENAME T_NEW TO T;

SELECT COUNT(*) FROM T;
SELECT COUNT(*) FROM T PARTITION (P1);
SELECT COUNT(*) FROM T PARTITION (P2);

如果在这两个分区基础，要把它分成更更多分分区，则需要指向split操作，如下所示：

alter table system.t split partition P1 at(to_date('2010-1-1','YYYY-MM-DD')) into (partition p_2009,partition t_upper);
SELECT * FROM T PARTITION (p_2009);
SELECT * FROM T PARTITION (t_upper);

alter table system.t split partition p_2009 at(to_date('2009-1-1','YYYY-MM-DD')) into (partition p_2008,partition p_2009);
SELECT * FROM T PARTITION (p_2008);
SELECT * FROM T PARTITION (p_2009);

alter table system.t split partition p_2008 at(to_date('2008-1-1','YYYY-MM-DD')) into (partition p_2007,partition p_2008);
alter table system.t split partition p_2007 at(to_date('2007-1-1','YYYY-MM-DD')) into (partition p_2006,partition p_2007);
alter table system.t split partition t_upper at(to_date('2011-1-1','YYYY-MM-DD')) into (partition p_2010,partition p_2011);

SELECT * FROM T PARTITION (p_2006);
SELECT * FROM T PARTITION (p_2007);
SELECT * FROM T PARTITION (p_2008);
SELECT * FROM T PARTITION (p_2009);
SELECT * FROM T PARTITION (p_2010);
SELECT * FROM T PARTITION (p_2011);
SELECT * FROM T PARTITION (P2);

---方法三：Oracle9i以上版本，利用在线重定义功能 （本测试是通过system用户登录并对rep用户下的对象进行了分区操作）

Drop table rep.t1;
CREATE TABLE rep.T1 (ID NUMBER PRIMARY KEY, TIME DATE);
INSERT INTO rep.T1
SELECT ROWNUM, SYSDATE - ROWNUM FROM DBA_OBJECTS WHERE ROWNUM <= 2000;
COMMIT;

begin
DBMS_REDEFINITION.CAN_REDEF_TABLE('REP', 'T1');
end;
--或
call DBMS_REDEFINITION.CAN_REDEF_TABLE('REP', 'T1');
--或在命令窗口中执行SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('REP', 'T1');
--SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('ERP', 'T1',dbms_redefinition.cons_use_pk);
PL/SQL 过程已成功完成。

CREATE TABLE rep.T1_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
(PARTITION P_2006 VALUES LESS THAN (TO_DATE('2007-1-1', 'YYYY-MM-DD')),
PARTITION P_2007 VALUES LESS THAN (TO_DATE('2008-1-1', 'YYYY-MM-DD')),
PARTITION P_2008 VALUES LESS THAN (TO_DATE('2009-7-1', 'YYYY-MM-DD')),
PARTITION P_2009 VALUES LESS THAN (TO_DATE('2010-7-1', 'YYYY-MM-DD')),
PARTITION P_2010 VALUES LESS THAN (TO_DATE('2011-7-1', 'YYYY-MM-DD')),
PARTITION P_2011 VALUES LESS THAN (MAXVALUE));

表已创建。
begin
DBMS_REDEFINITION.START_REDEF_TABLE('rep', 'T1', 'T1_NEW');
end;
--或在命令窗口中执行SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('rep', 'T1', 'T1_NEW');
PL/SQL 过程已成功完成。

begin
DBMS_REDEFINITION.FINISH_REDEF_TABLE('rep', 'T1', 'T1_NEW');
end;
--或在命令窗口中执行EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('rep', 'T1', 'T1_NEW');
PL/SQL 过程已成功完成。

SELECT COUNT(*) FROM rep.T1;
SELECT * FROM rep.T1 PARTITION (p_2006);
SELECT * FROM rep.T1 PARTITION (p_2007);
SELECT * FROM rep.T1 PARTITION (p_2008);
SELECT * FROM rep.T1 PARTITION (p_2009);
SELECT * FROM rep.T1 PARTITION (p_2010);
SELECT * FROM rep.T1 PARTITION (p_2011);

SELECT COUNT(*) FROM rep.t1_new;
SELECT * FROM rep.t1_new PARTITION (p_2006);