首页 » ORACLE 9i-23c » 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)评论关闭。