当前位置： 首页 > MIGRATE > 正文

expdp的compression测试

May202015
作者：huangtingzhong   发布：2015-05-20 16:11   分类：MIGRATE   阅读：576 views   抢沙发  
COMPRESSION 压缩参数说明

在10g中，数据泵的压缩只支持METADATA_ONLY这一种方式，在Oracle 11g中新增了几种压缩方法。
  • ALL 对导出的元数据和表数据都进行压缩，得到的导出文件是最小的，耗时也是最长的。
  • DATA_ONLY 仅对表数据进行压缩，对于大数据量的导出效果明显，会比METADATA_ONLY方式得到更小的压缩文件。
  • METADATA_ONLY 仅对元数据进行压缩，而不会对表数据进行压缩，这种压缩执行后效果一般不是很明显，不过速度比较快。
  • NONE 不进行任何的压缩，导出后的文件也是最大的。
  • DEFAULT 默认方式，即不指定COMPRESSION参数，会采用默认的压缩方式METADATA_ONLY。
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
With 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

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

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

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

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/?p=1073 | 认真就输

该日志由 huangtingzhong 于2015年05月20日发表在 MIGRATE 分类下， 通告目前不可用，你可以至底部留下评论。
原创文章转载请注明: expdp的compression测试 | 认真就输
关键字:
【上一篇】
【下一篇】

您可能还会对这些文章感兴趣！