使用导出导入(datapump)方式将普通表切换为分区表

--创建用户
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);

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_2012 VALUES LESS THAN (TO_DATE('01/01/2013', 'DD/MM/YYYY')) tablespace tbs1,
 PARTITION big_table_2013 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')) tablespace tbs2,
 PARTITION big_table_2014 VALUES LESS THAN (MAXVALUE)) tablespace tbs3;

--可以直接使用Insert方式来填充数据到分区表，如下
INSERT INTO big_table2
 SELECT * FROM big_table;

--该方式主要用于从不同的数据库迁移数据，比如源库源表为普通表，而目标库为分区表 
$ expdp leshami/xxx directory=db_dump_dir dumpfile=big_table.dmp logfile=exp_big_tb.log tables=big_table content=data_only

SQL> rename big_table to big_table_old;

Table renamed.

SQL> rename big_table2 to big_table;

Table renamed.

$ impdp leshami/xxx directory=db_dump_dir dumpfile=big_table.dmp logfile=imp__big_tb.log tables=big_table

EXEC DBMS_STATS.gather_table_stats('LESHAMI', 'BIG_TABLE', cascade => TRUE);
--下面是导入数据之后的结果
SQL> select table_name, partition_name,high_value,num_rows
 2 from user_tab_partitions where table_name='BIG_TABLE';

TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------------ ------------------------------ --------------------- ----------
BIG_TABLE2 BIG_TABLE_2012 TO_DATE(' 2013-01-01 3333
BIG_TABLE2 BIG_TABLE_2013 TO_DATE(' 2014-01-01 3334
BIG_TABLE2 BIG_TABLE_2014 MAXVALUE 3333

--如果数据无异常可以删除源表以便为分区表添加相应索引及约束，如果未删除源表，需要使用单独的索引，约束名等
SQL> drop table big_table;

Table dropped.

ALTER TABLE big_table ADD (
 CONSTRAINT big_table_pk PRIMARY KEY (id)
);

CREATE INDEX bita_created_date_i ON big_table(created_date) LOCAL;

CREATE INDEX bita_look_fk_i ON big_table(lookup_id) LOCAL;

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_table2;
/

5、后记