当我们对oracle进行数据导出的时候，最为常用的工具就是expdp了，但是有的时候担心导出目录空间不够，这时候我们可以用compression参数对dmp文件进行压缩。这个压缩的比例大概有多少呢，我们测试一下。

数据库版本

SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE    11.2.0.4.0      ProductionTNS for Linux: Version 11.2.0.4.0 - ProductionNLSRTL Version 11.2.0.4.0 - Production

1.构造一个表大概2.4G

SQL> select sum(bytes)/1024/1024/1024 from dba_segments where segment_name='TEST';SUM(BYTES)/1024/1024/1024-------------------------               2.44433594

2.创建导出的directory并授权

create directory dumpdir  as '/u01';grant read,write on directory dumpdir to system;

3.执行导出(不压缩)

expdp  \'/ as sysdba\' directory=dumpdir dumpfile=test.dmp logfile=test.log tables=scott.test;

4.执行导出(压缩)

expdp  \'/ as sysdba\' directory=dumpdir dumpfile=test.dmp logfile=test.log tables=scott.test compression=all

5.查看大小

[oracle@localhost u01]$ du -sh test.dmp2.1G    test.dmp

6.执行压缩

expdp  \'/ as sysdba\' directory=dumpdir dumpfile=test_compress.dmp logfile=test_compress.log tables=scott.test compression=all;

7.查看大小

[oracle@localhost u01]$ du -sh test_compress.dmp284M    test_compress.dmp

只有248M大小,大概缩小了近10倍。compression还有两个选项，data_only和metadata_only.只对数据或者元数据进行压缩。我们建几个索引看看压缩比如何。

8.查看索引和表大小

select sum(bytes)/1024/1024/1024 si,       segment_name 	from dba_segments where segment_name in ('IDX_TEMP','TEST','IDX_TEMP_COM')   group by segment_name;        SI SEGMENT_NAME---------- ---------------------------------------------------------------------------------1.05413818 IDX_TEMP_COM.374511719 IDX_TEMP2.44433594 TEST

9.再次导出(compression=data_only)

expdp  \'/ as sysdba\' directory=dumpdir dumpfile=test_compress.dmp logfile=test_compress.log tables=scott.test compression=data_only;

9.再次导出(compression=metadata_only)

expdp  \'/ as sysdba\' directory=dumpdir dumpfile=test_compress_meta.dmp logfile=test_compress_meta.log tables=scott.test compression=metadata_only;2.1G    test_compress_meta.dmp

10.导出压缩比较明显的还是对数据和索引的压缩，压缩比很高。