sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA > Oracle expdp impdp >

【学习笔记】Oracle数据泵expdp的compression压缩测试案例

时间:2016-11-05 22:30   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库数据泵的使用案例,详细介绍在Oracle数据泵导出时对数据进行压缩处理的全过程。

COMPRESSION 压缩参数说明

在10g中,数据泵的压缩只支持METADATA_ONLY这一种方式,在Oracle 11g中新增了几种压缩方法。

ALL :对导出的元数据和表数据都进行压缩,得到的导出文件是最小的,耗时也是最长的。
DATA_ONLY :仅对表数据进行压缩,对于大数据量的导出效果明显,会比METADATA_ONLY方式得到更小的压缩文件。
METADATA_ONLY :仅对元数据进行压缩,而不会对表数据进行压缩,这种压缩执行后效果一般不是很明显,不过速度比较快。
NONE :不进行任何的压缩,导出后的文件也是最大的。
DEFAULT :默认方式,即不指定COMPRESSION参数,会采用默认的压缩方式METADATA_ONLY。

1. ALL压缩方式导出数据

[oracle@rac3 ~]$ expdp hcxiang/willnice directory=HCXBAK dumpfile=bt02_all.dump tables=BT02 compression=all
Export: Release 11.2.0.4.0 – Production on Wed May 20 10:42:36 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
WiOracle???????oracleplus.netth the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HCXIANG"."SYS_EXPORT_TABLE_01": hcxiang/******** directory=HCXBAK dumpfile=bt02_all.dump tables=BT02 compression=all
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.250 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HCXIANG"."BT02" 119.5 MB 11139840 rows
Master table "HCXIANG"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HCXIANG.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/backup/bt02_all.dump
Job "HCXIANG"."SYS_EXPORT_TABLE_01" successfully completed at Wed May 20 10:43:15 2015 elapsed 0 00:00:38

2. DATA_ONLY压缩方式导出数据

[oracle@rac3 ~]$ expdp hcxiang/willnice directory=HCXBAK dumpfile=bt02_dataonly.dump tables=BT02 compression=data_only
Export: Release 11.2.0.4.0 – Production on Wed May 20 10:44:45 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HCXIANG"."SYS_EXPORT_TABLE_01": hcxiang/******** directory=HCXBAK dumpfile=bt02_dataonly.dump tables=BT02 compression=data_only
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.250 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HCXIANG"."BT02" 119.5 MB 11139840 rows
Master table "HCXIANG"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HCXIANG.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/backup/bt02_dataonly.dump
Job "HCXIANG"."SYS_EXPORT_TABLE_01" successfully completed at Wed May 20 10:45:15 2015 elapsed 0 00:00:29

3. METADATA_ONLY压缩方式导出数据

[oracle@rac3 ~]$ expdp hcxiang/willnice directory=HCXBAK dumpfile=bt02_metadataonly.dump tables=BT02 compression=metadata_only
Export: Release 11.2.0.4.0 – Production on Wed May 20 10:47:02 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HCXIANG"."SYS_EXPORT_TABLE_01": hcxiang/******** directory=HCXBAK dumpfile=bt02_metadataonly.dump tables=BT02 compression=metadata_only
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.250 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HCXIANG"."BT02" 1.034 GB 11139840 rows
Master table "HCXIANG"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HCXIANG.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/backup/bt02_metadataonly.dump
Job "HCXIANG"."SYS_EXPORT_TABLE_01" successfully completed at Wed May 20 10:47:16 2015 elapsed 0 00:00:13

4. NONE压缩方式导出数据

[oracle@rac3 ~]$ expdp hcxiang/willnice directory=HCXBAK dumpfile=bt02_none.dump tables=BT02 compression=none
Export: Release 11.2.0.4.0 – Production on Wed May 20 10:49:05 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HCXIANG"."SYS_EXPORT_TABLE_01": hcxiang/******** directory=HCXBAK dumpfile=bt02_none.dump tables=BT02 compression=none
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.250 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HCXIANG"."BT02" 1.034 GB 11139840 rows
Master table "HCXIANG"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HCXIANG.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/backup/bt02_none.dump
Job "HCXIANG"."SYS_EXPORT_TABLE_01" successfully completed at Wed May 20 10:49:18 2015 elapsed 0 00:00:12

5. DEFAULT压缩方式导出数据

[oracle@rac3 ~]$ expdp hcxiang/willnice directory=HCXBAK dumpfile=bt02_default.dump tables=BT02
Export: Release 11.2.0.4.0 – Production on Wed May 20 10:50:24 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HCXIANG"."SYS_EXPORT_TABLE_01": hcxiang/******** directory=HCXBAK dumpfile=bt02_default.dump tables=BT02
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.250 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HCXIANG"."BT02" 1.034 GB 11139840 rows
Master table "HCXIANG"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HCXIANG.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/backup/bt02_default.dump
Job "HCXIANG"."SYS_EXPORT_TABLE_01" successfully completed at Wed May 20 10:50:36 2015 elapsed 0 00:00:12

比较几种压缩方式导出的文件大小
[oracle@rac3 backup]$ ll -l
total 3497916
-rw-r—– 1 oracle oinstall 125358080 May 20 10:43 bt02_all.dump
-rw-r—– 1 oracle oinstall 125366272 May 20 10:45 bt02_dataonly.dump
-rw-r—– 1 oracle oinstall 1110372352 May 20 10:50 bt02_default.dump
-rw-r—– 1 oracle oinstall 1110372352 May 20 10:47 bt02_metadataonly.dump
-rw-r—– 1 oracle oinstall 1110380544 May 20 10:49 bt02_none.dump
可以看到对于ALL压缩方式,将1.2G的导出文件压缩到了120M,压缩比达到10%,效果还是比较不错的。
本文固定链接: http://www.htz.pw/2015/05/20/expdp%e7%9a%84compression%e6%b5%8b%e8%af%95.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle数据泵expdp的compression压缩测试案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1180.html

Oracle研究中心

关键词:

Oracle数据泵expdp导出压缩的4种方式对比

Oracle expdp使用案例

Oracle expdp压缩使用方法

Oracle数据泵导出进行压缩