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

随着数据库数据量的不断增长，有些表需要由普通的堆表转换为分区表的模式。有几种不同的方法来对此进行操作，诸如导出表数据，然后创建分区表再导入数据到分区表；使用EXCHANGE PARTITION方式来转换为分区表以及使用DBMS_REDEFINITION来在线重定义分区表。本

随着数据库数据量的不断增长，有些表需要由普通的堆表转换为分区表的模式。有几种不同的方法来对此进行操作，诸如导出表数据，然后创建分区表再导入数据到分区表；使用EXCHANGE PARTITION方式来转换为分区表以及使用DBMS_REDEFINITION来在线重定义分区表。本文描述的是使用导出导入方式来实现，下面是具体的操作示例。

有关具体的dbms_redefinition在线重定义表的原理及步骤可参考：基于 dbms_redefinition 在线重定义表

1、主要步骤

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_<b>本文来源gao@!dai!ma.com搞$$代^@码5网@</b>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_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;

4、通过datapump方式导出导入数据到分区表

--该方式主要用于从不同的数据库迁移数据，比如源库源表为普通表，而目标库为分区表 $ expdp leshami/xxx directory=db_dump_dir dumpfile=big_table.dmp logfile=exp_big_tb.log tables=big_table content=data_onlySQL> 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_tableEXEC 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        3333BIG_TABLE2                     BIG_TABLE_2013                 TO_DATE(' 2014-01-01        3334BIG_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 ROWBEGIN   :new.created_date := TO_CHAR (SYSDATE, 'yyyymmdd hh24:mi:ss');END tr_bf_big_table2;/5、后记

更多参考

有关Oracle RAC请参考

有关Oracle 网络配置相关基础以及概念性的问题请参考：

有关基于用户管理的备份和备份恢复的概念请参考

有关RMAN的备份恢复与管理请参考

有关ORACLE体系结构请参考

喜欢 (0)赏

[搞代码]

分享 (0)