Oracle 11g Data Pump expdp compression option to reduce the export dump file

_____________________________________________________________________________________________________________________

Oracle 11g provides different types of data compression techniques. Compression is the option to achieve the data compression in data pump. There are 4 options available with compression parameter.
  • ALL: Both metadata and data are compressed.
  • DATA_ONLY: Only data is compressed.
  • METADATA_ONLY: Only metadata is compressed. This is the default setting.
  • NONE: Nothing is compressed
In Oracle 10g data pump, there is no data compression option; only METADATA compression available with 10g. In Oracle 11g we don’t need to use UNIX compression options as it is available with data pump itself.
Example without compression:
Time taken: 9Mins Dumpfile size: 22507520Byte~22MB

$ date;expdp test/test directory=exp_dir dumpfile=withoutcomp.dmp schemas=test logfile=withoutcomp.log;date
Tue Jul 19 05:50:15 PDT 2011

Export: Release 11.1.0.7.0 - 64bit Production on Tuesday, 19 July, 2011 5:50:15

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPORT_SCHEMA_01":  test/******** directory=exp_dir dumpfile=withoutcomp.dmp schemas=test logfile=withoutcomp.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 27.56 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."EXAMPLE_INVI"                     10.00 MB   95274 rows
. . exported "TEST"."EXAMPLE_TBL"                      9.491 MB   95274 rows
. . exported "TEST"."EXAMPLE_PARTITION"                837.4 KB   95120 rows
. . exported "TEST"."EXAMPLE":"EXAMPLE_P1"             441.3 KB   49999 rows
. . exported "TEST"."EXAMPLE":"EXAMPLE_P2"             408.3 KB   45121 rows
. . exported "TEST"."DEPT"                             5.945 KB       4 rows
. . exported "TEST"."EMP1"                             5.875 KB       2 rows
. . exported "TEST"."EMP2"                             5.890 KB       3 rows
. . exported "TEST"."EXAMPLE_CHAR"                     5.445 KB       1 rows
. . exported "TEST"."ERRORLOG"                             0 KB       0 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/test/withoutcomp.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 05:59:21

Tue Jul 19 05:59:23 PDT 2011
$ ls -ltr withoutcomp.dmp
-rw-r-----   1 oracle   dba      22507520 Jul 19 05:59 withoutcomp.dmp
Example with compression
Time taken: 8Mins Dumpfile size: 3039232 Byte~3MB
$ date;expdp test/test directory=exp_dir compression=all dumpfile=withcomp.dmp schemas=test logfile=withcomp.log;date
Tue Jul 19 06:04:29 PDT 2011

Export: Release 11.1.0.7.0 - 64bit Production on Tuesday, 19 July, 2011 6:04:29

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPORT_SCHEMA_01":  test/******** directory=exp_dir compression=all dumpfile=withcomp.dmp schemas=test logfile=withcomp.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 27.56 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."EXAMPLE_INVI"                     1.386 MB   95274 rows
. . exported "TEST"."EXAMPLE_TBL"                      1.192 MB   95274 rows
. . exported "TEST"."EXAMPLE_PARTITION"                72.51 KB   95120 rows
. . exported "TEST"."EXAMPLE":"EXAMPLE_P1"             45.39 KB   49999 rows
. . exported "TEST"."EXAMPLE":"EXAMPLE_P2"             54.30 KB   45121 rows
. . exported "TEST"."DEPT"                             4.976 KB       4 rows
. . exported "TEST"."EMP1"                             4.890 KB       2 rows
. . exported "TEST"."EMP2"                             4.898 KB       3 rows
. . exported "TEST"."EXAMPLE_CHAR"                     4.812 KB       1 rows
. . exported "TEST"."ERRORLOG"                             0 KB       0 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/test/withcomp.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 06:12:25

Tue Jul 19 06:12:26 PDT 2011

$ ls -ltr withcomp.dmp

_____________________________________________________________________________________________________________________

0 comments:

Post a Comment

 

acehints.com Copyright 2011-17 All Rights Reserved | Site Map | Contact | Disclaimer | Google