使用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 ROW
BEGIN
   :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 ROW
BEGIN
   :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(*)
----------
     10000

DROP 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方式切割分区表

BIG_TABLE BIG_TABLE_2014 MAXVALUE 3333