结论 1

数据库导出时间排行

结论 2

数据库导入时间排行

第一部分导出

1. parallel=8 时的时间

real    2m11.154s
user    0m0.794s
sys     0m0.324s
命令为:
time expdp system/Test20131127@127.0.0.1/orcl  directory=dir schemas=testdb2103ora 
dumpfile=testdb2103ora8.dump logfile=20211101.txt version=12.2.0 parallel=8

2. 添加 compression=data_only 的时间.

real    3m24.708s
user    0m0.823s
sys     0m0.393s
0.9G大小
命令为:
time expdp system/Test20131127@127.0.0.1/orcl  directory=dir schemas=testdb2103ora 
dumpfile=testdb2103ora8_compression.dump logfile=20211101.txt version=12.2.0 parallel=8 compression=data_only

3. 不加并行, 不加压缩的时间为

real    5m20.344s
user    0m0.923s
sys     0m0.445s
4.6G大小
命令为:
time expdp system/Test20131127@127.0.0.1/orcl  directory=dir schemas=testdb2103ora 
dumpfile=testdb2103ora1.dump logfile=20211101.txt version=12.2.0

4. 仅导出部分表

real 3m29.177s
user 0m0.179s
sys 0m0.083s

3.4G大小
命令为:
time expdp  testdb2103ora02/Test6530@127.0.0.1/orcl directory=dir  schemas=testdb2103ora02 
dumpfile=testdb2103ora02_onlyTEST1.dump  include=TABLE:\" in \(select table_name from user_tables 
where table_name like \'TEST3\%\' or table_name like \'TEST1\%\' or table_name like \'TEST2\%\' \) \"

5. 不导出统计信息的耗时

real 3m49.547s
user 0m1.088s
sys 0m0.483s

大小 4.6G
命令为: time expdp  testdb2103ora02/Test6530@127.0.0.1/orcl directory=dir  schemas=testdb2103ora02 
dumpfile=testdb2103ora02_02.dump   exclude=statistics

6. 不导统计信息,并且设置并行度为4

real 3m3.440s
user 0m0.864s
sys 0m0.380s

大小 4.6G
命令为: time expdp  testdb2103ora02/Test6530@127.0.0.1/orcl directory=dir  schemas=testdb2103ora02 
dumpfile=testdb2103ora02_02_parallel4.dump   exclude=statistics parallel=4

7. 仅导出部分表又排除部分表

理论上这个是最好的命令 虽然时间稍微长一些. 但是数据量大的情况下 应该效果最好.

real 3m40.542s
user 0m0.225s
sys 0m0.102s

命令为:
time expdp  testdb2103ora02/Test6530@127.0.0.1/orcl directory=dir  schemas=testdb2103ora02 
dumpfile=testdb2103ora02_onlyTEST1_WithOutSOME.dump  include=TABLE:\" in \(select table_name from user_tables 
where \(table_name like \'TEST3\%\' or table_name like \'TEST1\%\' or table_name like \'TEST2\%\'\) 
and table_name \<\> \'TEST1DOCDBCONTENT\'  and table_name not like \'TEST1AUDIT\%\' \) \"

第二部分 导入时间验证

1. 直接导入不带任何参数

real    32m18.937s
user    0m0.652s
sys     0m0.308s
命令为:
time impdp testdb2103ora02/Test6530@127.0.0.1/orcl directory=dir remap_schema=testdb2103ora:testdb2103ora02 
remap_tablespace=testdb2103ora:testdb2103ora02 logfile=1.txt

2. 导入加并行.

real 27m3.534s
user 0m0.750s
sys 0m0.335s

命令为:
time impdp testdb2103ora03/Test6530@127.0.0.1/orcl directory=dir  
remap_schema=testdb2103ora:testdb2103ora03  remap_tablespace=testdb2103ora:testdb2103ora03 
dumpfile=testdb2103ora1.dump parallel=8 logfile=2.txt

3. 导入并行加导入的是压缩后的文件

real 29m26.895s
user 0m0.781s
sys 0m0.251s

命令为:
time impdp testdb2103ora04/Test6530@127.0.0.1/orcl directory=dir  
remap_schema=testdb2103ora:testdb2103ora04  remap_tablespace=testdb2103ora:testdb2103ora04  
dumpfile=testdb2103ora8_compression.dump parallel=8 logfile=3.txt

4. 不并行,但是不导入统计信息

real 11m37.259s
user 0m0.755s
sys 0m0.331s

命令为:
time impdp testdb2103ora05/Test6530@127.0.0.1/orcl directory=dir  
remap_schema=testdb2103ora:testdb2103ora05  remap_tablespace=testdb2103ora:testdb2103ora05 
dumpfile=testdb2103ora1.dump exclude=statistics  logfile=4.txt

5. 不并行不压缩,但是采用replace 表的方式执行导入

real 40m58.508s
user 0m0.743s
sys 0m0.409s

命令为:
time impdp testdb2103ora02/Test6530@127.0.0.1/orcl directory=dir  
remap_schema=testdb2103ora:testdb2103ora02  remap_tablespace=testdb2103ora:testdb2103ora02 
dumpfile=testdb2103ora1.dump table_exists_action=replace  logfile=1.txt

6. 仅导入部分表信息且不导统计信息

real 3m17.278s
user 0m0.205s
sys 0m0.075s

命令为: time impdp testdb2103ora02/Test6530@127.0.0.1/orcl directory=dir  
remap_schema=testdb2103ora:testdb2103ora02  remap_tablespace=testdb2103ora:testdb2103ora02 
dumpfile=testdb2103ora02_onlyTEST1.dump table_exists_action=replace  logfile=$now.txt exclude=statistics

7. 导入部分表并且导入统计信息

real 3m5.084s
user 0m0.203s
sys 0m0.063s

命令为:
time impdp testdb2103ora02/Test6530@127.0.0.1/orcl directory=dir  
remap_schema=testdb2103ora:testdb2103ora02  remap_tablespace=testdb2103ora:testdb2103ora02  
dumpfile=testdb2103ora02_onlyTEST1.dump table_exists_action=replace  logfile=$now.txt

8. 导入部分表并且使用parallel=4的情况

real 1m57.631s
user 0m0.161s
sys 0m0.070s

命令为:
time impdp testdb2103ora02/Test6530@127.0.0.1/orcl directory=dir  
remap_schema=testdb2103ora:testdb2103ora02  remap_tablespace=testdb2103ora:testdb2103ora02 
dumpfile=testdb2103ora02_onlyTEST1.dump table_exists_action=replace parallel=4 logfile=$now.txt

9. 导入部分表并且已经排除部分表并且parallel=4的情况

注意 理论上结合导出时第七个文件进行导入应该是效果是最好的,时间应该最短, 
但是我这边的机器没有大量数据所以效果不明显.
real 1m54.474s
user 0m0.159s
sys 0m0.072s

命令为:
time impdp testdb2103ora02/Test6530@127.0.0.1/orcl directory=dir  
remap_schema=testdb2103ora:testdb2103ora02  remap_tablespace=testdb2103ora:testdb2103ora02
dumpfile=testdb2103ora02_onlyTEST1_WithOutSOME.dump table_exists_action=replace parallel=4