大表裡有資料時表分割槽參考指令碼

近期關於表分割槽進行一定學習和參考網上的部分資料的基礎上，對原表有資料情況時的三種分割槽方法進行部分補充：

--方法一，利用原表重建分割槽表（本測試是透過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;

[@more@]

--方法二：使用交換分割槽的方法（本測試是透過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);