Oracle 分区表 导出导入 迁移

最新推荐文章于 2022-04-25 18:32:11 发布
最新推荐文章于 2022-04-25 18:32:11 发布
阅读量151 收藏
点赞数
文章标签： 数据库

在Blog ：分区表总结　里提到一种创建分区表的方法。使用导出导入。

Oracle 分区表 总结

http://blog.csdn.net/tianlesoftware/archive/2009/10/23/4717318.aspx

这种方法的步骤是：

（1）将普通表dump出来

（2）创建分区表

（3）将dump 文件导入数据。

分区表的迁移和这个步骤差不多。有2点要注意：

（1）分区表导出的dump 文件比普通表导出的大。

（2）导入分区表的时间要比普通表的时间要长。

补充一些exp/imp,expdp/impdp 与分区表有关的知识：使用exp -help 查看：

example: imp scott/tiger ignore=y tables=(emp,dept) full=n

or tables=(t1:p1,t1:p2), if t1 is partitioned table

example: exp scott/tiger grants=y tables=(emp,dept,mgr)

or tables=(t1:p1,t1:p2), if t1 is partitioned table

example: expdp scott/tiger dumpfile=scott.dmp directory=dmpdir schemas=scott or tables=(t1:p1,t1:p2), if t1 is partitioned table

example: impdp scott/tiger directory=dmpdir dumpfile=scott.dmp

一些优化参考Blog：

exp/imp 与 expdp/impdp 对比 及使用中的一些优化事项

http://blog.csdn.net/tianlesoftware/archive/2010/12/23/6093973.aspx

迁移分区表的步骤如下：

（1）导出分区表，可以使用exp或者expdp

（2）建立新的分区表

（3）导入分区表。

A）如果是imp，加ignore=y 参数，该参数会忽略创建表时的错误并继续加载数据。

B）如果是impdp，加 table_exists_action=append参数.

table_exists_action: action to take if imported object already exists. valid keywords: (skip), append, replace and truncate.

示例1：使用exp/imp

分区表：

create table pdba (id, time) partition by range (time)

(partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),

partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),

partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),

partition p4 values less than (maxvalue))

as select id, time from sys.dba;

SQL> select partition_name from user_tab_partitions where table_name='PDBA';

PARTITION_NAME

------------------------------

P1

P2

P3

P4

SQL> select count(*) from pdba partition(p1);

COUNT(*)

----------

1718285

SQL> select count(*) from pdba partition(p2);

COUNT(*)

----------

183667

SQL> select count(*) from pdba partition(p3);

COUNT(*)

----------

188701

SQL> select count(*) from pdba partition(p4);

COUNT(*)

----------

622582

SQL>

1. 导出表或者某个分区

（1）导出整个表：

C:/Users/Administrator.DavidDai>exp 'sys/sys as sysdba' tables=pdba file='d:/partition.dmp' log='d:/partition.log'

Export: Release 11.2.0.1.0 - Production on 星期四 3月 3 15:29:42 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的表通过常规路径...

. . 正在导出表 PDBA

. . 正在导出分区 P1导出了 1718285 行

. . 正在导出分区 P2导出了 183667 行

. . 正在导出分区 P3导出了 188701 行

. . 正在导出分区 P4导出了 622582 行

成功终止导出, 没有出现警告。

（2）导出某个分区：

C:/Users/Administrator.DavidDai>exp 'sys/sys as sysdba' tables=pdba:p4 file='d:/partition_p4.dmp' log='d:/partition_p4.log'

Export: Release 11.2.0.1.0 - Production on 星期四 3月 3 15:30:09 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的表通过常规路径...

. . 正在导出表 PDBA

. . 正在导出分区 P4导出了 622582 行

成功终止导出, 没有出现警告。

说明：开始用的分区表是11g的Interval 分区表，结果用exp导，报：

EXP-00006: internal inconsistency error
EXP-00000: Export terminated unsuccessfully

exp 不支持11g的新特性。参考：

Exporting System or Composite Partitioned Table Using Classic Export Gives EXP-6 AND EXP-0 [ID 762774.1]

http://blog.csdn.net/tianlesoftware/archive/2011/03/03/6220799.aspx

2. 创建分区表

2.1 可以使用一下语句获取表的DDL语句：

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u;

更多参考：

ORACLE 使用DBMS_METADATA.GET_DDL获取DDL语句

http://blog.csdn.net/tianlesoftware/archive/2009/11/25/4868007.aspx

2.2 使用imp 语句获取：

C:/Users/Administrator.DavidDai>imp 'sys/sys as sysdba' tables=pdba indexfile='D:/table.sql' file='d:/partition.dmp' ignore=y

Import: Release 11.2.0.1.0 - Production on 星期四 3月 3 15:49:47 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

经由常规路径由 EXPORT:V11.02.00 创建的导出文件

已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入

. . 正在跳过分区 "PDBA":"P1"

. . 正在跳过分区 "PDBA":"P2"

. . 正在跳过分区 "PDBA":"P3"

. . 正在跳过分区 "PDBA":"P4"

成功终止导入, 没有出现警告。

这里我们在imp上加了个参数：indexfile='D:/table.sql'，这条imp语句只会在对应的文件里生成分区表的ddl 语句。 然后编辑创建好就可以了。

最简单的方法就是使用第三方的工具，如Toad，直接就能查到表的定义语句了。

3. 导入分区数据

我们在第一步导出里做了2种，一个是导出全表，另一个是导出一个分区。我们分别导入验证。

3.1 导入一个分区

C:/Users/Administrator.DavidDai>imp 'sys/sys as sysdba' tables=pdba:p4 file='d:/partition_p4.dmp' ignore=y

Import: Release 11.2.0.1.0 - Production on 星期四 3月 3 15:58:27 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

经由常规路径由 EXPORT:V11.02.00 创建的导出文件

已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入

. 正在将 SYS 的对象导入到 SYS

. 正在将 SYS 的对象导入到 SYS

. . 正在导入分区 "PDBA":"P4"导入了 622582 行

成功终止导入, 没有出现警告。

SQL> select count(*) from pdba partition(p4);

COUNT(*)

----------

622582

SQL> select count(*) from pdba partition(p1);

COUNT(*)

----------

0

3.2 导入整个表

导入之前先把P4分区的数据truncate掉：

SQL> alter table pdba truncate partition p4;

表被截断。

SQL> select count(*) from pdba partition(p4);

COUNT(*)

----------

0

C:/Users/Administrator.DavidDai>imp 'sys/sys as sysdba' tables=pdba file='d:/partition.dmp' ignore=y

Import: Release 11.2.0.1.0 - Production on 星期四 3月 3 16:01:08 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

经由常规路径由 EXPORT:V11.02.00 创建的导出文件

已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入

. 正在将 SYS 的对象导入到 SYS

. 正在将 SYS 的对象导入到 SYS

. . 正在导入分区 "PDBA":"P1"导入了 1718285 行

. . 正在导入分区 "PDBA":"P2"导入了 183667 行

. . 正在导入分区 "PDBA":"P3"导入了 188701 行

. . 正在导入分区 "PDBA":"P4"导入了 622582 行

成功终止导入, 没有出现警告。

示例2：使用expdp/impdp

1. 导出dump文件

create directory dump as 'd:/backup';

grant read, write on directory dump to system;

（1）整个表

C:/Users/Administrator.DavidDai>Expdp system/system DIRECTORY=dump DUMPFILE=partition.dmp TABLES=PDBA logfile=exp.log;

Export: Release 11.2.0.1.0 - Production on 星期四 3月 3 16:18:15 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

启动 "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** DIRECTORY=dump DUMPFILE=partition.dmp TABLES=PDBA logfile=exp.log;

正在使用 BLOCKS 方法进行估计...

处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA

使用 BLOCKS 方法的总估计: 61 MB

处理对象类型 TABLE_EXPORT/TABLE/TABLE

处理对象类型 TABLE_EXPORT/TABLE/PRE_TABLE_ACTION

. . 导出了 "SYSTEM"."PDBA":"P1" 31.12 MB 1718285 行

. . 导出了 "SYSTEM"."PDBA":"P4" 11.28 MB 622582 行

. . 导出了 "SYSTEM"."PDBA":"P3" 3.422 MB 188701 行

. . 导出了 "SYSTEM"."PDBA":"P2" 3.331 MB 183667 行

已成功加载/卸载了主表 "SYSTEM"."SYS_EXPORT_TABLE_01"

******************************************************************************

SYSTEM.SYS_EXPORT_TABLE_01 的转储文件集为:

D:/BACKUP/PARTITION.DMP

作业 "SYSTEM"."SYS_EXPORT_TABLE_01" 已于 16:18:34 成功完成

（2）一个分区

C:/Users/Administrator.DavidDai>Expdp system/system DIRECTORY=dump DUMPFILE=partition_p4.dmp TABLES=PDBA:P4 logfile=exp_p4.log;

Export: Release 11.2.0.1.0 - Production on 星期四 3月 3 16:19:23 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

启动 "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** DIRECTORY=dump DUMPFILE=partition_p4.dmp TABLES=PDBA:P4 logfile=exp_p4.log;

正在使用 BLOCKS 方法进行估计...

处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA

使用 BLOCKS 方法的总估计: 14 MB

处理对象类型 TABLE_EXPORT/TABLE/TABLE

处理对象类型 TABLE_EXPORT/TABLE/PRE_TABLE_ACTION

. . 导出了 "SYSTEM"."PDBA":"P4" 11.28 MB 622582 行

已成功加载/卸载了主表 "SYSTEM"."SYS_EXPORT_TABLE_01"

******************************************************************************

SYSTEM.SYS_EXPORT_TABLE_01 的转储文件集为:

D:/BACKUP/PARTITION_P4.DMP

作业 "SYSTEM"."SYS_EXPORT_TABLE_01" 已于 16:19:32 成功完成

2. 创建分区表

用dbms_metadate.get_ddl() 直接获取就可以了，方法同示例1.

3. 导入dump文件

（1）导入一个分区

C:/Users/Administrator.DavidDai>impdp system/system DIRECTORY=dump DUMPFILE=partition_p4.dmp TABLES=PDBA:P4 logfile=imp_p4.log table_exists_action=append

-- 注意这个参数，后面不用加分号，直接回车就执行了。

Import: Release 11.2.0.1.0 - Production on 星期四 3月 3 16:24:15 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

已成功加载/卸载了主表 "SYSTEM"."SYS_IMPORT_TABLE_01"

启动 "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** DIRECTORY=dump DUMPFILE=partition_p4.dmp TABLES=PDBA logfile=imp_p4.log table_exists_action=append

处理对象类型 TABLE_EXPORT/TABLE/TABLE

ORA-39152: 表 "SYSTEM"."PDBA" 已存在。由于附加了 table_exists_action, 数据将附加到现有表, 但是将跳过所有相关元数据。

处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA

. . 导入了 "SYSTEM"."PDBA":"P4" 11.28 MB 622582 行

作业 "SYSTEM"."SYS_IMPORT_TABLE_01" 已经完成, 但是有 1 个错误 (于 16:24:21 完成)

（2）导入整个表

C:/Users/Administrator.DavidDai>impdp system/system DIRECTORY=dump DUMPFILE=partition.dmp TABLES=PDBA logfile=imp.log table_exists_action=append

Import: Release 11.2.0.1.0 - Production on 星期四 3月 3 16:26:51 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

已成功加载/卸载了主表 "SYSTEM"."SYS_IMPORT_TABLE_01"

启动 "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** DIRECTORY=dump DUMPFILE=partition.dmp TABLES=PDBA logfile=imp.log table_exists_action=append

处理对象类型 TABLE_EXPORT/TABLE/TABLE

ORA-39152: 表 "SYSTEM"."PDBA" 已存在。由于附加了 table_exists_action, 数据将附加到现有表, 但是将跳过所有相关元数据。

处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA

. . 导入了 "SYSTEM"."PDBA":"P1" 31.12 MB 1718285 行

. . 导入了 "SYSTEM"."PDBA":"P4" 11.28 MB 622582 行

. . 导入了 "SYSTEM"."PDBA":"P3" 3.422 MB 188701 行

. . 导入了 "SYSTEM"."PDBA":"P2" 3.331 MB 183667 行

作业 "SYSTEM"."SYS_IMPORT_TABLE_01" 已经完成, 但是有 1 个错误 (于 16:27:02 完成)

------------------------------------------------------------------------------

Blog： http://blog.csdn.net/tianlesoftware

网上资源： http://tianlesoftware.download.csdn.net

相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx

DBA1 群：62697716(满); DBA2 群：62697977(满)

DBA3 群：62697850 DBA 超级群：63306533;

聊天 群：40132017

--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请

iteye_20309
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
导入导出 Oracle 分区表数据
04-28
导入导出 Oracle 分区表数据
oracle11G 存在分区的表中的数据导出
zhzhiqun的专栏
10-08 218
数据库版本: oracle 11.1.2 环境：centos5.8 64 数据库中存在多个表有分区的情况。所以在数据库的备份时发生了错误。 Export done in UTF8 character set and UTF8 NCHAR character set server uses AL32UTF8 character set (possible charset con...
oracle11g expdp impdp 分区表重映射导出导入数据迁移方案
12-06
oracle expdp impdp 分区表重映射导出导入 数据迁移方案，以SI01用户为例子，将用户分区表导出后，将分区表重映射到新的表空间，完成数据迁移和检查。照方案例子按步去做，一定能成功。
分区表导入导出[未完]
皓月如我的专栏
03-07 1081
[EXP/IMP] 导出分区： exp iscs/naritech_zhjkb tables=di_change_event:P4,di_change_event:P5 file=d:/partition_p4.dmp log=d:/partition_p4.log 使用imp工具生成创建分区表的DDL语句： imp iscs/naritech_zhjkb tables=di_change
Oracle数据库导入导出工具
09-04
- 数据迁移：当需要将数据从一个Oracle实例迁移到另一个实例时，导入导出工具是关键。 - 数据备份：定期备份数据库以防止数据丢失，确保业务连续性。 - 数据恢复：在系统故障或数据错误时，通过备份数据进行恢复...
Oracle 导出导入迁移方案V1.4.docx
09-24
Oracle 数据泵导出导入迁移方案是一种高效的数据迁移技术，尤其适用于Oracle 10g、11g和12c版本的数据库系统。本方案详细介绍了数据泵的工作原理、使用场景、兼容性，以及一系列关键参数的说明和实例，旨在提供一个...
oracle_分区表数据定期迁移到其他数据库测试方案
08-26
本文档中的迁移方案旨在实现将Oracle分区表中的数据定期迁移到另一个数据库。这样的需求可能出于多种考虑，比如数据归档、负载均衡或数据同步等。 ### 三、Oracle 分区表迁移方案概述 该方案主要包括以下几个步骤...
oracle expdp 导出分区表的部分分区的数据
河南骏的博客
04-26 8925
expdp justin/justin dumpfile=justin_2011_04_a.dmp directory=pump logfile=2011_04_a.log tables=justin:P110401,justin:P110402,justin:P110403,justin:P110404,justin:P110405,justin:P110406,justin:P110407,j
从oracle库导出数据到postgresql库的ora2pg工具使用手册
u010652906的专栏
09-05 1146
方案一 kettle(推荐)-pdi-ce 方案二 ora2pg（备用） 工具安装 1.下载 2.到官网下载 3.到官网下载 4.到官网下载
oracle分区表分区导出,Oracle分区表数据的导入与导出
weixin_36168927的博客
04-02 4209
Oracle分区表的使用在一定程度上提高了对表数据的存储及处理性能，在查询性能和数据存储安全上体现了其特有的优势，因此很好的利用分区表，在一定程度上有利于数据库性能的优化。而Oracle分区表数据的导入导出也成了这些工作中不可忽略的一部分。分区表的导入导出同普通表的导入导出方式没有什么很大区别，只不过在导入导出时需要考虑到分区的特殊性，如：分区索引，将分区迁移到普通表，或使用原始分区表导入到新的分...
【导出导入】IMPDP table_exists_action 参数的应用
weixin_33989780的博客
04-15 173
当使用IMPDP完成数据库导入时，如遇到表已存在时，Oracle提供给我们如下四种处理方式： a.忽略（SKIP，默认行为）； b.在原有数据基础上继续增加（APPEND）； c.先DROP表，然后创建表，最后完成数据插入（REPLACE）； d.先TRUNCATE，再完成数据插入（TRUNCATE）。 下面做实验来看看几种方式的效果： =========...
IMPDP table_exists_action 参数的应用
欧辰的专栏
12-21 8208
当使用IMPDP完成数据库导入时，如遇到表已存在时，Oracle提供给我们如下四种处理方式： a.忽略（SKIP，默认行为）； b.在原有数据基础上继续增加（APPEND）； c.先DROP表，然后创建表，最后完成数据插入（REPLACE）； d.先TRUNCATE，再完成数据插入（TRUNCATE）。 下面做实验来看看几种方式的效果： ========================
分区表入无分区的数据库
12-10 175
10G版本以前分区表导入的方法:分区表在导入时，一般来说，有两种情况1、在导入数据库中，与被导入表的分区相关的表空间已对等建立好（表空间名也是与exp数据库中一致的），那么这种情况，与普通表的imp操作是一样的2、但多数情况下，...
Oracle数据导入导出
面朝大海，春暖花开
10-18 451
该命令在“开始菜单>>运行>>CMD”中执行 一、数据导出（exp.exe） 1、将数据库orcl完全导出，用户名system，密码accp，导出到d:\daochu.dmp文件中 exp system/accp@orcl file=d:\daochu.dmp full=y   2、将数据库orcl中scott用户的对象导出 exp scott/accp@orcl file=d:\da
oracle实验记录 （使用exp/imp）
cuiye1492的博客
09-28 336
使用exp/imp C:\>exp xh/a123@xh help=y 看下helpExport: Release 10.2.0.1.0 - Production on 星期五 9月 25 16:09:5...
Oracle 数据泵导入导出分区表及避坑指南
ChunkWen的博客
04-25 3084
Oracle 数据泵导入导出分区表及避坑指南 分区表的导入导出 首先根据emp表创建一个分区表，emp表如下： SCOTT@dborcl1> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'; SCOTT@dborcl1> desc emp; Name Null? Type ------------------------------------------------------------
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值