首页 » ORACLE » oracle11g Expdp COMPRESSION(数据泵压缩增强)

oracle11g Expdp COMPRESSION(数据泵压缩增强)

今天看oracle11g新特性发现宣称11G,用了新的zlib算法在压缩上改变很多,可以比oracle10g压缩算法快40%,空间减少20%,准不准不先不说,测试看应用有什么变化吧,小张实战开始

目标:
在10g r2和11Gr2 建立相同的表,插入一部分数据分别用expdp导出

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

sys@ORCL> conn anbob/anbob
Connected.
anbob@ORCL> create table testcomp(id int,contx varchar(1000));

Table created.

anbob@ORCL>
  1  begin
  2  for i in 1..10000 loop
  3  insert into testcomp values(i,lpad(i,100,'0'));
  4  end loop;
  5  commit;
  6* end;
anbob@ORCL> /

PL/SQL procedure successfully completed.

anbob@ORCL> select segment_name,bytes from user_segments
  2  where segment_name='TESTCOMP';

SEGMENT_NAME                         BYTES
----------------------------------- ----------
TESTCOMP                              2097152

anbob@ORCL> EXIT
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
--11g 建表省略,建好后所占大小相同(segment size)
[oracle@orazhang ~]$ expdp anbob/anbob directory=expdir dumpfile=testcomp10g.dump tables=testcomp COMPRESSION=METADATA_ONLY

Export: Release 10.2.0.1.0 - Production on 星期三, 17 8月, 2011 14:33:54

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "ANBOB"."SYS_EXPORT_TABLE_01":  anbob/******** directory=expdir dumpfile=testcomp10g.dump tables=testcomp COMPRESSION=METADATA_ONLY
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ANBOB"."TESTCOMP"                          1.044 MB   10000 rows
Master table "ANBOB"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ANBOB.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/testcomp10g.dump
Job "ANBOB"."SYS_EXPORT_TABLE_01" successfully completed at 14:34:12

[oracle@orazhang backup]$ cd /u01/app/oracle/
[oracle@orazhang oracle]$ ll
-rw-r----- 1 oracle oinstall 1159168 Aug 17 14:34 testcomp10g.dump

[oracle@orazhang oracle]$ expdp help=yes

Export: Release 10.2.0.1.0 - Production on 星期三, 17 8月, 2011 14:35:59

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

   Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:

   Format:  expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
   Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword               Description (Default)
------------------------------------------------------------------------------
ATTACH                Attach to existing job, e.g. ATTACH [=job name].
COMPRESSION           Reduce size of dumpfile contents where valid
                      keyword values are: (METADATA_ONLY) and NONE.

[oracle@orazhang oracle]$ cd /oracle11g/
[oracle@orazhang oracle11g]$ ls
[oracle@orazhang oracle11g]$ . ./ora11 

[oracle@orazhang oracle11g]$ expdp help=yes

Export: Release 11.2.0.1.0 - Production on 星期三 8月 17 14:37:02 2011

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

The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

   Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:

   Format:  expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
   Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

------------------------------------------------------------------------------

The available keywords and their descriptions follow. Default values are listed within square brackets.

ATTACH
Attach to an existing job.
For example, ATTACH=job_name.

COMPRESSION
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.

[oracle@orazhang oracle11g]$ expdp anbob/anbob directory=expdir dumpfile=testcomp11g.dump tables=testcomp COMPRESSION=METADATA_ONLY  

Export: Release 11.2.0.1.0 - Production on 星期三 8月 17 14:37:53 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
启动 "ANBOB"."SYS_EXPORT_TABLE_01":  anbob/******** directory=expdir dumpfile=testcomp11g.dump tables=testcomp COMPRESSION=METADATA_ONLY
正在使用 BLOCKS 方法进行估计...
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 2 MB
处理对象类型 TABLE_EXPORT/TABLE/TABLE
. . 导出了 "ANBOB"."TESTCOMP"                          1.044 MB   10000 行
已成功加载/卸载了主表 "ANBOB"."SYS_EXPORT_TABLE_01"
******************************************************************************
ANBOB.SYS_EXPORT_TABLE_01 的转储文件集为:
  /backup/testcomp11g.dump
作业 "ANBOB"."SYS_EXPORT_TABLE_01" 已于 14:38:03 成功完成

[oracle@orazhang backup]$ ll
-rw-r----- 1 oracle oinstall  1179648 Aug 17 14:38 testcomp11g.dump

[oracle@orazhang backup]$ expdp anbob/anbob directory=expdir file=testcomp11_2g.dump tables=testcomp COMPRESSION=METADATA_ONLY

Export: Release 11.2.0.1.0 - Production on 星期三 8月 17 14:45:44 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=testcomp11_2g.dump" Location: Command Line, Replaced with: "dumpfile=testcomp11_2g.dump"
Legacy Mode has set reuse_dumpfiles=true parameter.
启动 "ANBOB"."SYS_EXPORT_TABLE_01":  anbob/******** directory=expdir dumpfile=testcomp11_2g.dump tables=testcomp COMPRESSION=METADATA_ONLY reuse_dumpfiles=true
正在使用 BLOCKS 方法进行估计...
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 2 MB
处理对象类型 TABLE_EXPORT/TABLE/TABLE
. . 导出了 "ANBOB"."TESTCOMP"                          1.044 MB   10000 行
已成功加载/卸载了主表 "ANBOB"."SYS_EXPORT_TABLE_01"
******************************************************************************
ANBOB.SYS_EXPORT_TABLE_01 的转储文件集为:
  /backup/testcomp11_2g.dump
作业 "ANBOB"."SYS_EXPORT_TABLE_01" 已于 14:45:49 成功完成

[oracle@orazhang backup]$ ll
-rw-r----- 1 oracle oinstall  1179648 Aug 17 14:38 testcomp11g.dump
-rw-r----- 1 oracle oinstall  1179648 Aug 17 14:49 testcomp11_2g.dump

[oracle@orazhang backup]$ expdp anbob/anbob directory=expdir dumpfile=testcomp11g.dump tables=testcomp COMPRESSION=all

Export: Release 11.2.0.1.0 - Production on 星期三 8月 17 14:40:43 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
启动 "ANBOB"."SYS_EXPORT_TABLE_01":  anbob/******** directory=expdir dumpfile=testcomp11g.dump tables=testcomp COMPRESSION=all
正在使用 BLOCKS 方法进行估计...
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 2 MB
处理对象类型 TABLE_EXPORT/TABLE/TABLE
. . 导出了 "ANBOB"."TESTCOMP"                          42.14 KB   10000 行
已成功加载/卸载了主表 "ANBOB"."SYS_EXPORT_TABLE_01"
******************************************************************************
ANBOB.SYS_EXPORT_TABLE_01 的转储文件集为:
  /backup/testcomp11g.dump
作业 "ANBOB"."SYS_EXPORT_TABLE_01" 已于 14:40:49 成功完成

[oracle@orazhang backup]$ ll
-rw-r----- 1 oracle oinstall    65536 Aug 17 14:40 testcomp11g.dump

note:
1,11G的COMPRESSION参数值有增加
2,11G的dumpfile可以写成file
3,10g的METADATA_ONLY压缩大小为1159168bytes,11g相同压缩大小为1179648bytes
4,11g的ALL压缩大小仅为65536bytes

以上仅供参考,转载请声明出处www.anbob.com

打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. Carolina Albe | #1
    2011-12-21 at 03:58

    Great blog right here! Additionally your website loads up very fast! What web host are you the usage of? Can I get your associate link in your host? I wish my website loaded up as fast as yours lol