使用exchange方式切換普通表到分區表

      隨著資料庫資料量的不斷增長，有些表需要由普通的堆錶轉換為分區表的模式。有幾種不同的方法來對此進行操作，諸如匯出表資料，然後建立分區表再匯入資料到分區表；使用EXCHANGE PARTITION方式來轉換為分區表以及使用DBMS_REDEFINITION來線上重定義分區表。本文描述的是使用EXCHANGE PARTITION方式來實現，下面是具體的操作樣本。

      有關具體的dbms_redefinition線上重定義表的原理及步驟可參考：基於 dbms_redefinition 線上重定義表
      有關使用DBMS_REDEFINITION線上重定義分區表可參考：使用DBMS_REDEFINITION線上切換普通表到分區表
      有關分區表的描述請參考：Oracle 分區表

 

1、主要步驟
    a、為新的分區表準備相應的資料表空間
    b、基於源表中繼資料建立分區表以及相關索引、約束等
    c、使用exchange方式將普通表切換為分區表
    d、更正相關索引及約束名等(可省略)
    e、使用split根據需要將分區表分割為多個不同的分區
    f、收集統計資訊

 

2、準備環境    

--建立使用者SQL> create user leshami identified by xxx;SQL> grant dba to leshami;--建立示範需要用到的資料表空間SQL> create tablespace tbs_tmp datafile '/u02/database/SYBO2/oradata/tbs_tmp.dbf' size 10m autoextend on;SQL> alter user leshami default tablespace tbs_tmp;SQL> create tablespace tbs1 datafile '/u02/database/SYBO2/oradata/tbs1.dbf' size 10m autoextend on;SQL> create tablespace tbs2 datafile '/u02/database/SYBO2/oradata/tbs2.dbf' size 10m autoextend on;SQL> create tablespace tbs3 datafile '/u02/database/SYBO2/oradata/tbs3.dbf' size 10m autoextend on;SQL> conn leshami/xxx-- 建立一個lookup表CREATE TABLE lookup (  id            NUMBER(10),  description   VARCHAR2(50));--添加主鍵約束ALTER TABLE lookup ADD (  CONSTRAINT lookup_pk PRIMARY KEY (id));--插入資料INSERT INTO lookup (id, description) VALUES (1, 'ONE');INSERT INTO lookup (id, description) VALUES (2, 'TWO');INSERT INTO lookup (id, description) VALUES (3, 'THREE');COMMIT;--建立一個用於切換到分區的大表CREATE TABLE big_table (  id            NUMBER(10),  created_date  DATE,  lookup_id     NUMBER(10),  data          VARCHAR2(50));--填充資料到大表DECLARE  l_lookup_id    lookup.id%TYPE;  l_create_date  DATE;BEGIN  FOR i IN 1 .. 10000 LOOP    IF MOD(i, 3) = 0 THEN      l_create_date := ADD_MONTHS(SYSDATE, -24);      l_lookup_id   := 2;    ELSIF MOD(i, 2) = 0 THEN      l_create_date := ADD_MONTHS(SYSDATE, -12);      l_lookup_id   := 1;    ELSE      l_create_date := SYSDATE;      l_lookup_id   := 3;    END IF;        INSERT INTO big_table (id, created_date, lookup_id, data)    VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);  END LOOP;  COMMIT;END;/--為大表添加主、外鍵約束，索引，以及添加觸發器等.ALTER TABLE big_table ADD (  CONSTRAINT big_table_pk PRIMARY KEY (id));CREATE INDEX bita_created_date_i ON big_table(created_date);CREATE INDEX bita_look_fk_i ON big_table(lookup_id);ALTER TABLE big_table ADD (  CONSTRAINT bita_look_fk  FOREIGN KEY (lookup_id)  REFERENCES lookup(id));CREATE OR REPLACE TRIGGER tr_bf_big_table   BEFORE UPDATE OF created_date   ON big_table   FOR EACH ROWBEGIN   :new.created_date := TO_CHAR (SYSDATE, 'yyyymmdd hh24:mi:ss');END tr_bf_big_table;/--收集統計資訊EXEC DBMS_STATS.gather_table_stats('LESHAMI', 'LOOKUP', cascade => TRUE);EXEC DBMS_STATS.gather_table_stats('LESHAMI', 'BIG_TABLE', cascade => TRUE);

3、建立分區表

CREATE TABLE big_table2 (     id            NUMBER(10),     created_date  DATE,     lookup_id     NUMBER(10),     data          VARCHAR2(50)   )   PARTITION BY RANGE (created_date)   (PARTITION big_table_2014 VALUES LESS THAN (MAXVALUE) tablespace tbs3);  ALTER TABLE big_table2 ADD (  CONSTRAINT big_table_pk2 PRIMARY KEY (id));CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;ALTER TABLE big_table2 ADD (  CONSTRAINT bita_look_fk2  FOREIGN KEY (lookup_id)  REFERENCES lookup(id));--觸發器也需要單獨添加到分區表CREATE OR REPLACE TRIGGER tr_bf_big_table2              --Author: Leshami   BEFORE UPDATE OF created_date                        --Blog   : http://blog.csdn.net/leshami   ON big_table2   FOR EACH ROWBEGIN   :new.created_date := TO_CHAR (SYSDATE, 'yyyymmdd hh24:mi:ss');END tr_bf_big_table2;/

4、使用exchange切換為分區表

--下面的這個命令就是通過exchange方式來直接將普通表來切換為分區表ALTER TABLE big_table2  EXCHANGE PARTITION big_table_2014  WITH TABLE big_table  WITHOUT VALIDATION  UPDATE GLOBAL INDEXES;  SQL> select count(*) from big_table2;  COUNT(*)----------     10000DROP TABLE big_table;RENAME big_table2 TO big_table;ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;ALTER INDEX big_table_pk2 RENAME TO big_table_pk;ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;ALTER TRIGGER tr_bf_big_table2 RENAME TO tr_bf_big_table;

5、使用split方式分割分區表

ALTER TABLE big_table  SPLIT PARTITION big_table_2014 AT (TO_DATE('31-DEC-2012 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))  INTO (PARTITION big_table_2012 tablespace tbs1 ,        PARTITION big_table_2014)  UPDATE GLOBAL INDEXES;ALTER TABLE big_table  SPLIT PARTITION big_table_2014 AT (TO_DATE('31-DEC-2013 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))  INTO (PARTITION big_table_2013 tablespace tbs2,        PARTITION big_table_2014)  UPDATE GLOBAL INDEXES;--收集統計資訊，如果表很大的話，需要考慮使用並行度，採樣值--對於上述的操作中，本地分區索引和資料存放區在指定的資料表空間，存在混用情形；對於全域索引則儲存在預設資料表空間，--上面提到的2種情形，可以根據需要作相應調整EXEC DBMS_STATS.gather_table_stats('LESHAMI', 'BIG_TABLE', cascade => TRUE);--驗證結果SQL> col HIGH_VALUE format a45 wrappedSQL> select table_name, partition_name,high_value,num_rows from user_tab_partitions  2  where table_name='BIG_TABLE';TABLE_NAME      PARTITION_NAME       HIGH_VALUE                                      NUM_ROWS--------------- -------------------- --------------------------------------------- ----------BIG_TABLE       BIG_TABLE_2012       TO_DATE(' 2012-12-31 23:59:59', 'SYYYY-MM-DD        3333                                     HH24:MI:SS', 'NLS_CALENDAR=GREGORIABIG_TABLE       BIG_TABLE_2013       TO_DATE(' 2013-12-31 23:59:59', 'SYYYY-MM-DD        3334                                     HH24:MI:SS', 'NLS_CALENDAR=GREGORIABIG_TABLE       BIG_TABLE_2014       MAXVALUE                                            3333     

本文參考：Partitioning an Existing Table using EXCHANGE PARTITION

    

更多參考

有關Oracle RAC請參考
     使用crs_setperm修改RAC資源的所有者及許可權
     使用crs_profile管理RAC資源設定檔
     RAC 資料庫的啟動與關閉
     再說 Oracle RAC services
     Services in Oracle Database 10g
     Migrate datbase from single instance to Oracle RAC
     Oracle RAC 串連到指定執行個體
     Oracle RAC 負載平衡測試(結合伺服器端與用戶端)
     Oracle RAC 伺服器端串連負載平衡(Load Balance)
     Oracle RAC 用戶端串連負載平衡(Load Balance)
     ORACLE RAC 下非預設連接埠監聽配置(listener.ora tnsnames.ora) 
     ORACLE RAC 監聽配置 (listener.ora tnsnames.ora) 
     配置 RAC 負載平衡與容錯移轉
     CRS-1006 , CRS-0215 故障一例 
     基於Linux (RHEL 5.5) 安裝Oracle 10g RAC 
     使用 runcluvfy 校正Oracle RAC安裝環境

有關Oracle 網路設定相關基礎以及概念性的問題請參考：
     配置非預設連接埠的動態服務註冊 
     配置sqlnet.ora限制IP訪問Oracle 
     Oracle 監聽器日誌配置與管理 
     設定 Oracle 監聽器密碼(LISTENER)
     配置ORACLE 用戶端串連到資料庫

有關基於使用者管理的備份和備份恢複的概念請參考
     Oracle 冷備份
     Oracle 熱備份
     Oracle 備份恢複概念
     Oracle 執行個體恢複
     Oracle 基於使用者管理恢複的處理
     SYSTEM 資料表空間管理及備份恢複
     SYSAUX資料表空間管理及恢複
     Oracle 基於備份控制檔案的恢複(unsing backup controlfile)

有關RMAN的備份恢複與管理請參考
     RMAN 概述及其體繫結構
     RMAN 配置、監控與管理
     RMAN 備份詳解
     RMAN 還原與恢複
     RMAN catalog 的建立和使用
     基於catalog 建立RMAN儲存指令碼
     基於catalog 的RMAN 備份與恢複
     RMAN 備份路徑困惑
     使用RMAN實現異機備份恢複(WIN平台)
     使用RMAN遷移檔案系統資料庫到ASM
     linux 下RMAN備份shell指令碼
     使用RMAN遷移資料庫到異機

有關ORACLE體繫結構請參考
     Oracle 資料表空間與資料檔案
     Oracle 密碼檔案
     Oracle 參數檔案
     Oracle 聯機重做記錄檔(ONLINE LOG FILE)
     Oracle 控制檔案(CONTROLFILE)
     Oracle 歸檔日誌
     Oracle 復原(ROLLBACK)和撤銷(UNDO)
     Oracle 資料庫執行個體啟動關閉過程
     Oracle 10g SGA 的自動化管理
     Oracle 執行個體和Oracle資料庫(Oracle體繫結構)