联系：QQ(5163721)
标题：使用exp+pipe将导出文件生成压缩包（文本数据的话，空间通常节省10倍左右）
作者：Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]
有时候我们的存储空间不够，一个exp会产生一个很大的dmp文件，因此，我们就像exp的时候直接生成一个压缩包，那么管道就可以派上用场了，O(∩_∩)O哈哈~
10g以后，可以使用expdp compression，例如：
[oracle@lunar ~]$ expdp lunar/lunar file=expdp_ff.dmp compression=all tables=ff Export: Release 11.2.0.3.0 - Production on Sun Oct 27 01:48:15 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning and Real Application Testing options Legacy Mode Active due to the following parameters: Legacy Mode Parameter: "file=expdp_ff.dmp" Location: Command Line, Replaced with: "dumpfile=expdp_ff.dmp" Legacy Mode has set reuse_dumpfiles=true parameter. Legacy Mode has set nologfile=true parameter. Starting "LUNAR"."SYS_EXPORT_TABLE_01": lunar/******** dumpfile=expdp_ff.dmp compression=all tables=ff reuse_dumpfiles=true nologfile=true Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 15 MB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "LUNAR"."FF" 1.678 MB 141112 rows Master table "LUNAR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for LUNAR.SYS_EXPORT_TABLE_01 is: /u01/app/oracle/admin/travel/dpdump/expdp_ff.dmp Job "LUNAR"."SYS_EXPORT_TABLE_01" successfully completed at 01:48:24 [oracle@lunar ~]$ |
10g以前，就3条命令搞定：
mknod /tmp/exp_pipe p compress < /tmp/exp_pipe > exp_full.dmp.Z & exp lunar/lunar file=/tmp/exp_pipe log=exp_full.log full=y |
主意，linux里面可以使用gzip的，貌似compress需要单独安装，unix里面直接有compress
mknod /tmp/exp_pipe p gzip < /tmp/exp_pipe > exp_full.dmp.Z & exp lunar/lunar file=/tmp/exp_pipe log=exp_full.log full=y |
使用gzip的例子：
[oracle@lunar ~]$ ll total 14964 -rw-r--r-- 1 oracle oinstall 15286272 Oct 27 01:11 ff.dmp -rw-r--r-- 1 oracle oinstall 1165 Oct 26 22:15 filelist.txt -rw-r--r-- 1 oracle oinstall 229 Oct 26 22:14 log.bbd drwxr-xr-x 7 oracle oinstall 4096 Oct 27 00:35 test -rw-r--r-- 1 oracle oinstall 469 Feb 8 2013 travel.env [oracle@lunar ~]$ [oracle@lunar ~]$ [oracle@lunar ~]$ gzip < /tmp/exp_pipe > exp_ff.dmp.Z & [1] 13619 [oracle@lunar ~]$ exp lunar/lunar file=/tmp/exp_pipe log=exp_ff.log tables=ff Export: Release 11.2.0.3.0 - Production on Sun Oct 27 01:37:57 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning and Real Application Testing options Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) About to export specified tables via Conventional Path ... . . exporting table FF 141112 rows exported Export terminated successfully without warnings. [oracle@lunar ~]$ ll total 16604 -rw-r--r-- 1 oracle oinstall 1667612 Oct 27 01:37 exp_ff.dmp.Z -rw-r--r-- 1 oracle oinstall 473 Oct 27 01:37 exp_ff.log -rw-r--r-- 1 oracle oinstall 15286272 Oct 27 01:11 ff.dmp -rw-r--r-- 1 oracle oinstall 1165 Oct 26 22:15 filelist.txt -rw-r--r-- 1 oracle oinstall 229 Oct 26 22:14 log.bbd drwxr-xr-x 7 oracle oinstall 4096 Oct 27 00:35 test -rw-r--r-- 1 oracle oinstall 469 Feb 8 2013 travel.env [1]+ Done gzip < /tmp/exp_pipe > exp_ff.dmp.Z [oracle@lunar ~]$ |
可以看到大概15倍的压缩比，O(∩_∩)O哈哈~
使用compress的例子：
[oracle@ts01 tmp]$ mknod /tmp/exp_pipe p [oracle@ts01 tmp]$ ls -l /tmp/exp_pipe prw-r--r-- 1 oracle oinstall 0 Mar 16 12:05 /tmp/exp_pipe [oracle@ts01 tmp]$ compress < /tmp/exp_pipe > exp_full.dmp.Z & [1] 9099 [oracle@ts01 tmp]$ [oracle@ts01 tmp]$ jobs [1]+ Running compress </tmp/exp_pipe >exp_full.dmp.Z & [oracle@ts01 tmp]$ ls -lrt total 0 [oracle@ts01 tmp]$ ls -lrt total 0 [oracle@ts01 tmp]$ exp lunar/lunar file=/tmp/exp_pipe log=exp_full.log Export: Release 9.2.0.6.0 - Production on Thu Mar 16 12:06:10 2006 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0 - Production Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses ZHS16GBK character set (possible charset conversion) About to export specified users ... . exporting pre-schema procedural objects and actions EXP-00008: ORACLE error 4068 encountered ORA-04068: existing state of packages has been discarded ORA-04063: package body "SYS.LT_EXPORT_PKG" has errors ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 1 EXP-00083: The previous problem occurred when calling SYS.LT_EXPORT_PKG.schema_info_exp . exporting foreign function library names for user LUNAR . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions for user LUNAR About to export LUNAR's objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions . about to export LUNAR's tables via Conventional Path ... . . exporting table BBED 2 rows exported . . exporting table BIG_TABLE 1000000 rows exported . . exporting table BONUS 0 rows exported . . exporting table DEPT 4 rows exported . . exporting table DUMMY 1 rows exported . . exporting table EMP 14 rows exported . . exporting table LUNAR_TEST 1 rows exported . . exporting table RUN_STATS . . exporting table SALGRADE 5 rows exported . . exporting table SUBSCRIPTION_HISTORY . . exporting partition SUBNHIS_P1 1664 rows exported . . exporting partition SUBNHIS_P2 1792 rows exported . . exporting partition SUBNHIS_P3 1978 rows exported . . exporting partition SUBNHIS_P4 1890 rows exported . . exporting partition SUBNHIS_P5 1980 rows exported . . exporting partition SUBNHIS_P6 1920 rows exported . . exporting partition SUBNHIS_P7 1984 rows exported . . exporting partition SUBNHIS_P8 1984 rows exported . . exporting partition SUBNHIS_P9 1920 rows exported . . exporting partition SUBNHIS_P10 1984 rows exported . . exporting partition SUBNHIS_P11 1920 rows exported . . exporting partition SUBNHIS_P12 1984 rows exported . . exporting table T 20000 rows exported . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting referential integrity constraints . exporting triggers . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting materialized views . exporting snapshot logs . exporting job queues . exporting refresh groups and children . exporting dimensions . exporting post-schema procedural objects and actions EXP-00008: ORACLE error 4068 encountered ORA-04068: existing state of packages has been discarded ORA-04063: package body "SYS.LT_EXPORT_PKG" has errors ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 1 EXP-00083: The previous problem occurred when calling SYS.LT_EXPORT_PKG.schema_info_exp . exporting statistics Export terminated successfully with warnings. [oracle@ts01 tmp]$ [oracle@ts01 tmp]$ ls -lrt total 28488 -rw-r--r-- 1 oracle oinstall 3684 Mar 16 12:06 exp_full.log -rw-r--r-- 1 oracle oinstall 29133046 Mar 16 12:06 exp_full.dmp.Z [1]+ Done compress </tmp/exp_pipe >exp_full.dmp.Z [oracle@ts01 tmp]$ [oracle@ts01 tmp]$ [oracle@ts01 tmp]$ ls -lrt total 28488 -rw-r--r-- 1 oracle oinstall 3684 Mar 16 12:06 exp_full.log -rw-r--r-- 1 oracle oinstall 29133046 Mar 16 12:06 exp_full.dmp.Z [oracle@ts01 tmp]$ |
