环境
数据库:10.2.0.4
操作系统:sun solaris 10
主机内存:32G
主机CPU:32*150 MHz
测试说明
1.使用exp和expdp分别导出一张1827367条记录的普通表,共400多MB
2.使用imp和impdp分别将这种表导入
测试步骤
1.使用exp将表导出,消耗时间可以通过开始、结束时间计算
MGBOSS:/mgboss/work/guohua/ahdmp/expdump> date;exp dbaccadm/dbaccadm@ahcrmbdev file=user_exp.dmp log=user_exp.log tables='('UR_USER_INFO')';date;
2011年01月12日 星期三 17时14分20秒 CST
Export: Release 10.2.0.4.0 - Production on Wed Jan 12 17:14:20 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table UR_USER_INFO 1827367 rows exported
Export terminated successfully without warnings.
2011年01月12日 星期三 17时17分07秒 CST
注:消耗时间=((17*60+7)-(14*60+20))=167秒
2.使用expdp将表导出
MGBOSS:/mgboss/work/guohua/ahdmp/expdump> date;expdp dbaccadm/dbaccadm@ahcrmbdev directory=dump_guohua dumpfile=user_expdp.dmp logfile=user_expdp.log tables=UR_USER_INFO;date;
2011年01月12日 星期三 17时19分09秒 CST
Export: Release 10.2.0.4.0 - 64bit Production on Wednesday, 12 January, 2011 17:19:09
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "DBACCADM"."SYS_EXPORT_TABLE_01": dbaccadm/********@ahcrmbdev directory=dump_guohua dumpfile=user_expdp.dmp logfile=user_expdp.log tables=UR_USER_INFO
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 480 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "DBACCADM"."UR_USER_INFO" 412.2 MB 1827367 rows
Master table "DBACCADM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DBACCADM.SYS_EXPORT_TABLE_01 is:
/mgboss/work/guohua/ahdmp/expdump/user_expdp.dmp
Job "DBACCADM"."SYS_EXPORT_TABLE_01" successfully completed at 17:19:26
2011年01月12日 星期三 17时19分27秒 CST
MGBOSS:/mgboss/work/guohua/ahdmp/expdump> ls -l
total 1864454
-rw-r--r-- 1 mgboss crmapp 507217920 1月 12日 17:17 user_exp.dmp
-rw-r--r-- 1 mgboss crmapp 427 1月 12日 17:17 user_exp.log
-rw-r----- 1 oracle10 dba 432422912 1月 12日 17:19 user_expdp.dmp
-rw-r--r-- 1 oracle10 dba 1081 1月 12日 17:19 user_expdp.log
注:消耗时间=(27-9)=18秒
3.比对两次数据
SQL> select ((17*60+7)-(14*60+20)) "EXP Time(Minute)",(27-9) "EXPDP Time(Minute)",((17*60+7)-(14*60+20))/(27-9) "PRSENT" from dual
2 /
EXP Time(Minute) EXPDP Time(Minute) PRSENT
---------------- ------------------ ----------
167 18 9.27777778
SQL>
4.将该表drop掉,使用imp将该表数据导入库中
MGBOSS:/mgboss/work/guohua/ahdmp/expdump> rm *imp*
rm: user_impdp.log: override protection 644 (yes/no)? yes
MGBOSS:/mgboss/work/guohua/ahdmp/expdump> ls -l
total 1835258
-rw-r--r-- 1 mgboss crmapp 507217920 1月 12日 17:17 user_exp.dmp
-rw-r--r-- 1 mgboss crmapp 427 1月 12日 17:17 user_exp.log
-rw-r----- 1 oracle10 dba 432422912 1月 12日 17:19 user_expdp.dmp
-rw-r--r-- 1 oracle10 dba 1081 1月 12日 17:19 user_expdp.log
MGBOSS:/mgboss/work/guohua/ahdmp/expdump> date;imp dbaccadm/dbaccadm@ahcrmbdev file=user_exp.dmp log=user_imp.log tables='('UR_USER_INFO')';date;
2011年01月12日 星期三 17时42分12秒 CST
Import: Release 10.2.0.4.0 - Production on Wed Jan 12 17:42:12 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing DBACCADM's objects into DBACCADM
. importing DBACCADM's objects into DBACCADM
. . importing table "UR_USER_INFO" 1827367 rows imported
Import terminated successfully without warnings.
2011年01月12日 星期三 17时44分26秒 CST
6.将该表drop掉,使用impdp将该表数据导入库中
MGBOSS:/mgboss/work/guohua/ahdmp/expdump> date;impdp dbaccadm/dbaccadm@ahcrmbdev directory=dump_guohua dumpfile=user_expdp.dmp logfile=user_impdp.log tables=UR_USER_INFO;date;
2011年01月12日 星期三 17时45分14秒 CST
Import: Release 10.2.0.4.0 - 64bit Production on Wednesday, 12 January, 2011 17:45:14
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "DBACCADM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "DBACCADM"."SYS_IMPORT_TABLE_01": dbaccadm/********@ahcrmbdev directory=dump_guohua dumpfile=user_expdp.dmp logfile=user_impdp.log tables=UR_USER_INFO
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DBACCADM"."UR_USER_INFO" 412.2 MB 1827367 rows
Job "DBACCADM"."SYS_IMPORT_TABLE_01" successfully completed at 17:45:29
2011年01月12日 星期三 17时45分30秒 CST
SQL> SELECT ((44*60+26)-(42*60+12)) "IMP Time(Minute)",((45*60+30)-(45*60+14)) "IMPDP Time(Minute)",
2 ((44*60+26)-(42*60+12))/((45*60+30)-(45*60+14)) "PRSENT" from dual
3 /
IMP Time(Minute) IMPDP Time(Minute) PRSENT
---------------- ------------------ ----------
134 16 8.375
SQL>
结论:
1.两次比对结果可以看出,expdp比exp快将近10倍,impdp比imp快将近9倍
2.Oracle数据泵工具产生的dmp文件较EXP/IMP小一些,可能是进行了压缩 (^_^ 本人不确定哦,待高人指点,呵呵)