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也是在数据量比较大的时候会体现出高效率。
本文转自 ztfriend 51CTO博客，原文链接：http://blog.51cto.com/leonarding/1227491，如需转载请自行联系原作者
