Skip to content

Datapump compression parameter

by Mohamed Azar on December 11, 2010

Here I just like to show How compression datapump parameter working in Oracle 11g R2 ( see Below Screenshot How  size vary from others.)

Default: METADATA_ONLY

Purpose

Specifies which data to compress before writing to the dump file set

Syntax and Description

COMPRESSION={ALL | DATA_ONLY | METADATA_ONLY | NONE}
  • ALL enables compression for the entire export operation. The ALL option requires that the Oracle Advanced Compression option be enabled.
  • DATA_ONLY results in all data being written to the dump file in compressed format. The DATA_ONLY option requires that the Oracle Advanced Compression option be enabled.
  • METADATA_ONLY results in all metadata being written to the dump file in compressed format. This is the default.
  • NONE disables compression for the entire export operation.
Restrictions
  • To make full use of all these compression options, the COMPATIBLE initialization parameter must be set to at least 11.0.0.
  • The METADATA_ONLY option can be used even if the COMPATIBLE initialization parameter is set to 10.2.
  • Compression of data (using values ALL or DATA_ONLY) is valid only in the Enterprise Edition of Oracle Database 11g

For Example  , See Below Screenshot:

Compression =METADATA_ONLY


C:\Users\mazar>set oracle_sid=azardb

C:\Users\mazar>expdp scott/tiger@azardb dumpfile=compressmeta.dmp directory=data_pump_dir compression=metadata_only

Export: Release 11.2.0.1.0 - Production on Sat Dec 11 10:19:38 2010

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
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/********@azardb dumpfile=compressmeta.dmp directory=data_pump_dir compression=metadata_only
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
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/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.945 KB       4 rows
. . exported "SCOTT"."EMP"                               8.578 KB      14 rows
. . exported "SCOTT"."MYTEST"                            5.429 KB       1 rows
. . exported "SCOTT"."SALGRADE"                          5.875 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\COMPRESSMETA.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:21:05

Compression =ALL


C:\Users\mazar>expdp scott/tiger@azardb dumpfile=compressall.dmp directory=data_pump_dir compression=all

Export: Release 11.2.0.1.0 - Production on Sat Dec 11 10:21:59 2010

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
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/********@azardb dumpfile=compressall.dmp directory=data_pump_dir compression=all
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
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/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              4.984 KB       4 rows
. . exported "SCOTT"."EMP"                               5.625 KB      14 rows
. . exported "SCOTT"."MYTEST"                            4.789 KB       1 rows
. . exported "SCOTT"."SALGRADE"                          4.898 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\COMPRESSALL.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:22:41

Compression =DATA_ONLY


C:\Users\mazar>expdp scott/tiger@azardb dumpfile=compressdata.dmp directory=data_pump_dir compression=data_only

Export: Release 11.2.0.1.0 - Production on Sat Dec 11 10:23:23 2010

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
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/********@azardb dumpfile=compressdata.dmp directory=data_pump_dir compression=data_only
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
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/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              4.984 KB       4 rows
. . exported "SCOTT"."EMP"                               5.625 KB      14 rows
. . exported "SCOTT"."MYTEST"                            4.789 KB       1 rows
. . exported "SCOTT"."SALGRADE"                          4.898 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\COMPRESSDATA.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:24:02

Compression =NONE

C:\Users\mazar>expdp scott/tiger@azardb dumpfile=compressnone.dmp directory=data_pump_dir compression=none

Export: Release 11.2.0.1.0 - Production on Sat Dec 11 10:24:28 2010

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
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/********@azardb dumpfile=compressnone.dmp directory=data_pump_dir compression=none
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
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/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.945 KB       4 rows
. . exported "SCOTT"."EMP"                               8.578 KB      14 rows
. . exported "SCOTT"."MYTEST"                            5.429 KB       1 rows
. . exported "SCOTT"."SALGRADE"                          5.875 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\COMPRESSNONE.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:25:09

Screenshot, How size vary from every compression parameter.

Ref :

http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/dp_export.htm

From: Datapump
←
→
4 Comments Post a comment
  1. example #

    Export sizes are too small to make an accurate comparison – who has a DB whose export is like 200KB?

    March 27, 2013
    Reply
  2. Rama #

    Hi Azar,

    In exp its start exporting of table in ascending order(table start with A…Z), then what about in expdp.

    September 8, 2015
    Reply
    • Mohamed Azar #

      Did you tried?

      September 9, 2015
      Reply
      • Rama #

        Yeah..its exporting tables randomly…as i know it should export tables based on size..plz correct if i am wrong.

        September 9, 2015

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s

%d bloggers like this: