【原创】比较数据泵和exp/imp对相同数据导出/导入的性能差异

EXP/IMP 是数据库传统的导出导入工具，它有使用方便，在服务端和客户端都可用的优点，速度没有expdp快，功能没有expdp强大

EXP  工具帮助文档，我们可以参考帮助文档进行命令行配置

[oracle@leonarding1 ~]$ exp –help    或者    exp help=y

Export: Release 11.2.0.1.0 - Production onThu Jun 20 07:28:33 2013

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

You can let Export prompt you forparameters by entering the EXP

command followed by your username/password:

    Example: EXP SCOTT/TIGER

Or, you can control how Export runs byentering the EXP command followed

by various arguments. To specifyparameters, you use keywords:

    Format:  EXP KEYWORD=value rKEYWORD=(value1,value2,...,valueN)

    Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)

               or TABLES=(T1:P1,T1:P2), if T1is partitioned table

USERID must be the first parameter on thecommand line.

Keyword   Description (Default)     Keyword      Description (Default)

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

USERID    username/password         FULL         export entire file(N)

BUFFER    size of data buffer       OWNER        list of ownerusernames

FILE      output files (EXPDAT.DMP)  TABLES       list of table names

COMPRESS  import into one extent (Y) RECORDLENGTH length of IO record

GRANTS    export grants (Y)         INCTYPE      incremental exporttype

INDEXES   export indexes (Y)        RECORD       track incr. export(Y)

DIRECT    direct path (N)            TRIGGERS     export triggers (Y)

LOG       log file of screen output STATISTICS   analyze objects(ESTIMATE)

ROWS      export data rows (Y)      PARFILE      parameter filename

CONSISTENT cross-table consistency(N)CONSTRAINTS  export constraints (Y)

OBJECT_CONSISTENT    transaction set to read only during objectexport (N)

FEEDBACK             display progress every x rows (0)

FILESIZE             maximum size of each dump file

FLASHBACK_SCN        SCN used to set session snapshot backto

FLASHBACK_TIME       time used to get the SCN closest to thespecified time

QUERY                select clause used to export asubset of a table

RESUMABLE            suspend when a space related erroris encountered(N)

RESUMABLE_NAME       text string used to identify resumablestatement

RESUMABLE_TIMEOUT    wait time for RESUMABLE

TTS_FULL_CHECK       perform. full or partial dependency checkfor TTS

VOLSIZE              number of bytes to write to eachtape volume

TABLESPACES          list of tablespaces to export

TRANSPORT_TABLESPACE export transportabletablespace metadata (N)

TEMPLATE             template name which invokes iASmode export

Export terminated successfully withoutwarnings.

###################################################################

创建测试表leo1

LEO1@LEO1>set linesize 400 pagesize 999                                      格式化

LEO1@LEO1>drop table leo1 purge;                                                 清空环境

Table dropped.

LEO1@LEO1>create table leo1 as select *from dba_objects;     创建测试表leo1

Table created.

LEO1@LEO1>insert into leo1 select * fromleo1;

72543 rows created.

LEO1@LEO1>insert into leo1 select * fromleo1;

145086 rows created.

LEO1@LEO1>insert into leo1 select * fromleo1;

290172 rows created.

LEO1@LEO1>insert into leo1 select * fromleo1;

580344 rows created.

LEO1@LEO1>commit;

Commit complete.

LEO1@LEO1>insert into leo1 select * fromleo1;               

1160688 rows created.

LEO1@LEO1>commit;

Commit complete.

LEO1@LEO1>select count(*) from leo1;                      现在是232万

COUNT(*)

----------

  2321376

[oracle@leonarding1 ~]$ mkdir exp_dump                    创建一个导出文件的文件夹

[oracle@leonarding1 ~]$ exp leo1/leo1file='/home/oracle/exp_dump/leo1.dmp' tables=leo1 rows=y

Export: Release11.2.0.1.0 - Production on Fri Jun 21 06:38:28 2013

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

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

Export done in US7ASCII character set andAL16UTF16 NCHAR character set

server uses ZHS16GBK character set(possible charset conversion)

About to export specified tables viaConventional Path ...

. . exporting table                           LEO1    2321376 rows exported

Export terminated successfully withoutwarnings.  成功终止导出，没有出现告警

[oracle@leonarding1 ~]$ date

Fri Jun 21 06:39:40 CST 2013

导出从06:38:28开始到06:39:40结束，一共用时72秒

LEO1@LEO1>truncate table leo1;                           清空表，我再导入一下

Table truncated.

[oracle@leonarding1 ~]$ cd exp_dump/

[oracle@leonarding1 exp_dump]$ ll

total 261356

-rw-r--r-- 1 oracle oinstall 267362304 Jun21 06:39 leo1.dmp      这个就是我们导出的文件

[oracle@leonarding1 exp_dump]$ impleo1/leo1 file='/home/oracle/exp_dump/leo1.dmp' full=y ignore=y

Import: Release11.2.0.1.0 - Production on Fri Jun 21 06:45:04 2013

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

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

Export file created by EXPORT:V11.02.00 viaconventional path

import done in US7ASCII character set andAL16UTF16 NCHAR character set

import server uses ZHS16GBK character set(possible charset conversion)

. importing LEO1's objects into LEO1

. importing LEO1's objects into LEO1

. . importing table                         "LEO1"    2321376 rows imported

Import terminated successfully withoutwarnings.  成功终止导入，没有出现告警

[oracle@leonarding1 exp_dump]$ date

Fri Jun 21 06:48:26 CST 2013

导入从06:45:04开始到06:48:26结束，一共用时202秒

EXPDP/IMPDP 是Oracle推崇的数据泵导入导出工具，用于代替传统的EXP/IMP，只能在服务端使用，效率比EXP/IMP快几十倍，有续传功能和并行功能。这个工具始于Oracle10g，从Oracle11g开始不再提供老的EXP/IMP的咨询但工具还可以使用。

EXPDP工具的效率差不多比EXP快几倍

IMPDP工具的效率差不多比IMP快几十倍

所以说这两个工具适用于大数据导入导出的场景

导出文件的格式更接近于数据库本身的文件格式，避免了数据写入文件时的转换

直接路径加载，跳过SGA内存区，直接加载到高水位线之后

元数据metadata和数据data在导出的过程中可以重叠进行，提高导出的效率。

我们再来看看EXPDP/IMPDP的导出导入效果

在使用EXPDP/IMPDP之前我们需要定义一个目录对象，告知EXPDP/IMPDP工具导出的文件和导入的文件的存放目录

LEO1@LEO1>create directory exp_dump as'/home/oracle/exp_dump';

Directory created.

[oracle@leonarding1 exp_dump]$ expdpleo1/leo1 directory=exp_dump dumpfile=expdp_leo1.dmp tables=leo1;

Export: Release11.2.0.1.0 - Production on Fri Jun 21 07:07:09 2013

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

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

Starting"LEO1"."SYS_EXPORT_TABLE_01":  leo1/******** directory=exp_dumpdumpfile=expdp_leo1.dmp tables=leo1

Estimate in progress using BLOCKS method...

Processing object typeTABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 264MB

Processing object typeTABLE_EXPORT/TABLE/TABLE

. . exported"LEO1"."LEO1"                               223.9 MB 2321376 rows

Master table"LEO1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

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

Dump file set for LEO1.SYS_EXPORT_TABLE_01is:

/home/oracle/exp_dump/expdp_leo1.dmp

Job"LEO1"."SYS_EXPORT_TABLE_01" successfully completed at07:08:22

导出从07:07:09开始到07:08:22结束，一共用时73秒，导出文件大小223.9MB 行数2321376 rows，与exp时间相差1秒，expdp在数据量比较大的时候会体现出高效率。

现在我们impdp导入

LEO1@LEO1>drop table leo1;   先把表删除，因为如果表结构存在的话，impdp会认为元数据已存在报错，不可导入

Table dropped.

[oracle@leonarding1 exp_dump]$ impdpleo1/leo1 directory=exp_dump dumpfile=expdp_leo1.dmp

Import: Release11.2.0.1.0 - Production on Fri Jun 21 07:24:07 2013

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

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

Master table"LEO1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting"LEO1"."SYS_IMPORT_FULL_01":  leo1/******** directory=exp_dumpdumpfile=expdp_leo1.dmp

Processing object typeTABLE_EXPORT/TABLE/TABLE

Processing object typeTABLE_EXPORT/TABLE/TABLE_DATA

. . imported"LEO1"."LEO1"                               223.9 MB 2321376rows

Job"LEO1"."SYS_IMPORT_FULL_01" successfully completed at07:27:27

导入从07:24:07开始到07:27:27结束，一共用时200秒，导入文件大小223.9MB 行数2321376 rows，比imp时间节约了2秒，impdp也是在数据量比较大的时候会体现出高效率。