快速导出数据
1>EXAMPLE1
2亿
exp sms/sms file=smsmg_p7.dmp tables=SMSMG:M07 direct=y recordlength=65535 statistics=none
导出时间大约28分钟：
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production导出文件大小为59G左右：
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Direct Path ...
. . exporting table SMSMG
. . exporting partition M07 256466785 rows exported
Export terminated successfully without warnings.
42.33user 666.59system 28:02.65elapsed 42%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (27major+3131minor)pagefaults 0swaps
-rw-r--r-- 1 oracle oinstall 63306875535 Sep 25 23:27 smsmg_p7.dmp
主机为4颗4核 处理器：
exp的性能还是可以接受的。虽然expdp是Oracle 10g推荐的逻辑导出方式，但是仍然有很多人并不习惯这一方式，这与expdp的使用复杂度有关，我觉得expdp的directory方式应当简化，由参数行定义就好了，后台由数据库自动去维护。[root@sms proc]# cat cpuinfo |grep CPU
model name : Intel(R) Xeon(R) CPU E7330 @ 2.40GHz
model name : Intel(R) Xeon(R) CPU E7330 @ 2.40GHz
model name : Intel(R) Xeon(R) CPU E7330 @ 2.40GHz
model name : Intel(R) Xeon(R) CPU E7330 @ 2.40GHz
model name : Intel(R) Xeon(R) CPU E7330 @ 2.40GHz
model name : Intel(R) Xeon(R) CPU E7330 @ 2.40GHz
model name : Intel(R) Xeon(R) CPU E7330 @ 2.40GHz
model name : Intel(R) Xeon(R) CPU E7330 @ 2.40GHz
model name : Intel(R) Xeon(R) CPU E7330 @ 2.40GHz
model name : Intel(R) Xeon(R) CPU E7330 @ 2.40GHz
model name : Intel(R) Xeon(R) CPU E7330 @ 2.40GHz
model name : Intel(R) Xeon(R) CPU E7330 @ 2.40GHz
model name : Intel(R) Xeon(R) CPU E7330 @ 2.40GHz
model name : Intel(R) Xeon(R) CPU E7330 @ 2.40GHz
model name : Intel(R) Xeon(R) CPU E7330 @ 2.40GHz
model name : Intel(R) Xeon(R) CPU E7330 @ 2.40GHz
2>EXAMPLE 2
最近在做导出时比较了一下几种方式对于速度的影响，记录一下数据供参考。
nohup time exp sms/sms file=smsmg_p2.dmp tables=SMSMG:M02 & ******************** 1.3yi 50min
nohup time exp sms/sms file=smsmg_p3.dmp tables=SMSMG:M03 direct=y recordlength=65535 & *************1.8yi 22min
nohup time expdp sms/sms dumpfile=smsmg_p4.dmp directory=dataorg tables=SMSMG:M04 PARALLEL=2 & *****1.6yi 32Min
3>EXAMPLE3
单表测试,1363292行记录，116.6 MB左右数据
1.使用并行 expdp
花费时间:
Tue Apr 27 10:21:54 CST 2004 - Tue Apr 27 10:21:10 CST 2004 = 43秒
$ cat bak.sh
date
expdp eygle/eygle dumpfile=big_big_table.dmp directory=dpdata tables=big_big_table job_name=exptab parallel=4date
2.使用正常 expdp
花费时间:
Tue Apr 27 10:23:36 CST 2004 - Tue Apr 27 10:23:02 CST 2004 = 34 秒看来并行的差异需要更大的数据量的测试
$ cat bak2.sh date expdp eygle/eygle dumpfile=big_big_table2.dmp directory=dpdata tables=big_big_table job_name=exptab date
3.常规路径exp
花费时间:Tue Apr 27 10:27:00 CST 2004 - Tue Apr 27 10:24:54 CST 2004 = 2:06
这是花费时间最长的.
是 126/34 = 370.58823529411764705882352941176%expdp明显快于exp
$ cat bak3.sh date exp eygle/eygle file=big_big_table3.dmp tables=big_big_table date
4>直接路径exp：
cat bak4.sh date exp eygle/eygle file=big_big_table3.dmp tables=big_big_table direct=y date
4〉IMPORT 数据：
IMPDP远远优于imp
imp:
$ cat rev2.sh date imp eygle/eygle file=big_big_table3.dmp tables=big_big_table date
$ ./rev2.sh 407s
IMPDP:
花费时间: Tue Apr 27 11:07:06 CST 2004 - Tue Apr 27 11:06:40 CST 2004 = 26s
与imp相比这个速度实在是惊人.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/8568259/viewspace-2103742/ ，如需转载，请注明出处，否则将追究法律责任。
