前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >使用导出导入(datapump)方式将普通表切换为分区表

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

作者头像
Leshami
发布2018-08-13 14:59:53
9120
发布2018-08-13 14:59:53
举报
文章被收录于专栏:乐沙弥的世界

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

      有关具体的dbms_redefinition在线重定义表的原理及步骤可参考:基于 dbms_redefinition 在线重定义表       有关使用DBMS_REDEFINITION在线重定义分区表可参考:使用DBMS_REDEFINITION在线切换普通表到分区表       有关使用使用exchange方式可参考:使用exchange方式切换普通表到分区表       有关分区表的描述请参考:Oracle 分区表       有关分区表数据导入导出可参考:导入导出 Oracle 分区表数据

1、主要步骤     a、为新的分区表准备相应的表空间     b、基于源表元数据创建分区表     c、使用datapump方式导出原表数据然后再导入到分区表     d、收集统计信息,验证结果,为分区表添加索引约束等

2、准备环境

代码语言:javascript
复制
--创建用户
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、创建分区表

代码语言:javascript
复制
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方式导出导入数据到分区表

代码语言:javascript
复制
--该方式主要用于从不同的数据库迁移数据,比如源库源表为普通表,而目标库为分区表 
$ 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、后记       之所以用到了导出导入的方式,是因为源表与目标分区表位于不同的数据库,需要将源表数据迁移到新的分区表。当然使用带dblink的insert方式也可以完成上述功能。注意,在导入时,如果目标数据库的新分区表与原数据库源表表名一致,可以跳过本文描述的rename表名以及删除源表名的过程。其次,该种切换到分区表的方式简单易用,但难以保证数据一致性,通常使用在可空闲的表对象上进行操作。

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2014年06月25日,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
数据传输服务
腾讯云数据传输服务(Data Transfer Service,DTS)可帮助用户在业务不停服的前提下轻松完成数据库迁移上云,利用实时同步通道轻松构建高可用的数据库多活架构,通过数据订阅来满足商业数据挖掘、业务异步解耦等场景需求。同时,DTS 还提供私有化独立输出版本 DTS-DBbridge,支持异构数据库和同构数据库之间迁移和同步,可以帮助企业实现完整数据库迁移(如 Oracle)。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档