数据泵压缩 oracle,oracle11g Expdp COMPRESSION(数据泵压缩增强)-CSDN博客

今天看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

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

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

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

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: Release11.2.0.1.0- Production on 星期三 8月 17 14:45:44 2011

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

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

打赏

微信扫一扫，打赏作者吧～