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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google