比较数据泵和exp/imp对相同数据导出/导入的性能差异 - Oracle数据库栏目 - 红黑联盟
比较数据泵和exp/imp对相同数据导出/导入的性能差异
比较数据泵和exp/imp对相同数据导出/导入的性能差异
1.创建测试表语句：128W条数据，216M。
create table test as select * from dba_objects where rownum&10001;
for i in 1 .. 7 loop
insert into test select *
BYS@ bys001&select count(*)
& COUNT(*)
----------
& &1280000
Elapsed: 00:00:01.12
BYS@ bys001&select segment_name,segment_type,tablespace_name,extents,bytes/ MB,owner from dba_segments where segment_name='TEST' and owner='BYS';
SEGMENT_NAME & &SEGMENT_TYPE & & & TABLESPACE_NAME & &EXTENTS & & & & MB OWNER
--------------- ------------------ --------------- ---------- ---------- ----------
TEST & & & & & &TABLE & & & & & & &USERS & & & & & & & & & 98 & & & &216 BYS
一、导出测试：
1.使用EXP导出，用时30秒。
[oracle@oel-01 ~]$ exp bys/bys file='/home/oracle/test.dmp' tables=test rows=y
Export: Release 11.2.0.1.0 - Production on Mon Jul 29 17:01:48 2013
Copyright (c) , Oracle and/or its affiliates. &All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table & & & & & & & & & & & & & TEST & &1280000 rows exported
Export terminated successfully without warnings.
[oracle@oel-01 ~]$ date
Mon Jul 29 17:02:18 CST 2013
2.使用EXPDP导出：
要创建 exp_dump目录并在中指定。
[oracle@oel-01 ~]$ mkdir exp_dump
BYS@ bys001&create directory exp_dump as '/home/oracle/exp_dump';
Directory created.
Elapsed: 00:00:01.14
导出同样的数据，EXPDP用时1分13秒。
[oracle@oel-01 exp_dump]$ expdp bys/bys directory=exp_dump file=test.dmp tables=test
Export: Release 11.2.0.1.0 - Production on Mon Jul 29 17:31:51 2013
Copyright (c) ,
and/or its affiliates. &All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: &file=test.dmp& Location: Command Line, Replaced with: &dumpfile=test.dmp&
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting &BYS&.&SYS_EXPORT_TABLE_01&: &bys/******** directory=exp_dump dumpfile=test.dmp tables=test reuse_dumpfiles=true&
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 136 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported &BYS&.&TEST& & & & & & & & & & & & & & & & &111.6 MB 1280000 rows
Master table &BYS&.&SYS_EXPORT_TABLE_01& successfully loaded/unloaded
******************************************************************************
Dump file set for BYS.SYS_EXPORT_TABLE_01 is:
& /home/oracle/exp_dump/test.dmp
Job &BYS&.&SYS_EXPORT_TABLE_01& successfully completed at 17:33:04
########################
二、导入测试：
1. imp用时1分39秒
BYS@ bys001&
Table truncated.
Elapsed: 00:00:00.16
[oracle@oel-01 ~]$ imp bys/bys file='/home/oracle/test.dmp' full=y ignore=y
Import: Release 11.2.0.1.0 - Production on Mon Jul 29 17:21:16 2013
Copyright (c) , Oracle and/or its affiliates. &All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing BYS's objects into BYS
. importing BYS's objects into BYS
. . importing table & & & & & & & & & & & & &TEST& & &1280000 rows imported
Import terminated successfully without warnings.
[oracle@oel-01 ~]$ date
Mon Jul 29 17:22:55 CST 2013
2. &impdp导入用时1分20秒。
BYS@ bys001&
Table truncated.
Elapsed: 00:00:00.12
BYS@ bys001&d
Table dropped.
Elapsed: 00:00:00.05
[oracle@oel-01 exp_dump]$ impdp bys/bys directory=exp_dump dumpfile=test.dmp
Import: Release 11.2.0.1.0 - Production on Mon Jul 29 17:39:08 2013
Copyright (c) , Oracle and/or its affiliates. &All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table &BYS&.&SYS_IMPORT_FULL_01& successfully loaded/unloaded
Starting &BYS&.&SYS_IMPORT_FULL_01&: &bys/******** directory=exp_dump dumpfile=test.dmp&
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported &BYS&.&TEST& & & & & & & & & & & & & & & & &111.6 MB 1280000 rows
Job &BYS&.&SYS_IMPORT_FULL_01& successfully completed at 17:40:28
实验总结：
此次实验数据量不大，以上的每个工具的导入导出都测试两次，结果依然如上。
可能是因为数据量过小，导出时expdp比exp慢的较多。导入时impdp比imp快的也不太多。
EXPDP/IMPDP是Oracle推荐的数据泵导入导出工具，用于代替传统的EXP/IMP，只能在服务端使用，效率比EXP/IMP快几十倍，有续传功能和并行功能。
这个工具始于Oracle10g，从Oracle11g开始不再提供老的EXP/IMP的咨询但工具还可以使用。
EXPDP工具的效率差不多比EXP快几倍，IMPDP工具的效率差不多比IMP快几十倍，这两个工具适用于大数据导入导出的场景。
导出文件的格式更接近于数据库本身的文件格式，避免了数据写入文件时的转换
直接路径加载，跳过SGA内存区，直接加载到高水位线之后
元数据metadata和数据data在导出的过程中可以重叠进行，提高导出的效率。
在使用EXPDP/IMPDP之前需要定义一个目录对象，告知EXPDP/IMPDP工具导出的文件和导入的文件的存放目录The page is temporarily unavailable
nginx error!
The page you are looking for is temporarily unavailable.
Please try again later.
Website Administrator
Something has triggered an error on your
This is the default error page for
nginx that is distributed with
It is located
/usr/share/nginx/html/50x.html
You should customize this error page for your own
site or edit the error_page directive in
the nginx configuration file
/etc/nginx/nginx.conf.The page is temporarily unavailable
nginx error!
The page you are looking for is temporarily unavailable.
Please try again later.
Website Administrator
Something has triggered an error on your
This is the default error page for
nginx that is distributed with
It is located
/usr/share/nginx/html/50x.html
You should customize this error page for your own
site or edit the error_page directive in
the nginx configuration file
/etc/nginx/nginx.conf.The page is temporarily unavailable
nginx error!
The page you are looking for is temporarily unavailable.
Please try again later.
Website Administrator
Something has triggered an error on your
This is the default error page for
nginx that is distributed with
It is located
/usr/share/nginx/html/50x.html
You should customize this error page for your own
site or edit the error_page directive in
the nginx configuration file
/etc/nginx/nginx.conf.Oracle exp 和expdp 的区别
[问题点数：20分，结帖人xiaozhang8383]
Oracle exp 和expdp 的区别
[问题点数：20分，结帖人xiaozhang8383]
不显示删除回复
显示所有回复
显示星级回复
显示得分回复
只显示楼主
2011年4月 Oracle大版内专家分月排行榜第二
2011年2月 Oracle大版内专家分月排行榜第三2010年11月 Oracle大版内专家分月排行榜第三
本帖子已过去太久远了，不再提供回复功能。