Wednesday, 26 November 2014

Datapump compression parameter


Here I just like to show How compression datapump parameter working in Oracle 11g R2 ( see following demonstration 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

[oracle@TESTDB datapump]$ expdp test/test@TESTDB dumpfile=compressmeta.dmp directory=DATAPUMP_TEST compression=metadata_only

Export: Release 11.2.0.3.0 - Production on Wed Nov 26 12:32:31 2014

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, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPORT_SCHEMA_01":  test/********@TESTDB dumpfile=compressmeta.dmp directory=DATAPUMP_TEST compression=metadata_only

Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 32 KB
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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
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/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."DEPT"                               5.007 KB       1 rows
. . exported "TEST"."EMP"                                5.007 KB       1 rows
. . exported "TEST"."CLASS"                                  0 KB       0 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
  /data1/datapump/compressmeta.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:32:36

Compression =ALL


[oracle@TESTDB datapump]$ expdp test/test@TESTDB dumpfile=compressall.dmp directory=DATAPUMP_TEST compression=all

Export: Release 11.2.0.3.0 - Production on Wed Nov 26 12:33:09 2014

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, OLAP, Data Mining and Real Application Testing options

Starting "TEST"."SYS_EXPORT_SCHEMA_01":  test/********@TESTDB dumpfile=compressall.dmp directory=DATAPUMP_TEST compression=all
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 32 KB
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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
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/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."DEPT"                               4.687 KB       1 rows
. . exported "TEST"."EMP"                                4.687 KB       1 rows
. . exported "TEST"."CLASS"                                  0 KB       0 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
  /data1/datapump/compressall.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:33:11

Compression =DATA_ONLY

[oracle@TESTDB datapump]$ expdp test/test@TESTDB dumpfile=compressdata.dmp directory=DATAPUMP_TEST compression=data_only

 Export: Release 11.2.0.3.0 - Production on Wed Nov 26 12:33:30 2014

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, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPOR_SCHEMA_01":  test/********@TESTDB dumpfile=compressdata.dmpdirectory=DATAPUMP_TEST compression=data_only
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 32 KB
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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
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/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."DEPT"                               4.687 KB       1 rows
. . exported "TEST"."EMP"                                4.687 KB       1 rows
. . exported "TEST"."CLASS"                                  0 KB       0 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
  /data1/datapump/compressdata.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:33:34

Compression =NONE

[oracle@TESTDB datapump]$ expdp test/test@TESTDB dumpfile=compressnone.dmp directory=DATAPUMP_TEST compression=none

 Export: Release 11.2.0.3.0 - Production on Wed Nov 26 12:33:59 2014
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, OLAP, Data Mining and Real Application Testing options

Starting "TEST"."SYS_EXPORT_SCHEMA_01":  test/********@TESTDB dumpfile=compressnone.dmp directory=DATAPUMP_TEST compression=none
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 32 KB
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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
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/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."DEPT"                               5.007 KB       1 rows
. . exported "TEST"."EMP"                                5.007 KB       1 rows
. . exported "TEST"."CLASS"                                  0 KB       0 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
  /data1/datapump/compressnone.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:34:02

Detail File size comparison

Compare how size vary from every compression parameter.

[oracle@NVMBD1BZY150D00 datapump]$ ls -lrth

-rw-r-----. 1 oracle oinstall 192K Nov 26 12:32 compressmeta.dmp
-rw-r-----. 1 oracle oinstall  68K Nov 26 12:33 compressall.dmp
-rw-r-----. 1 oracle oinstall  72K Nov 26 12:33 compressdata.dmp
-rw-r-----. 1 oracle oinstall 196K Nov 26 12:34 compressnone.dmp



I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!
Posted by at
Label ,

1 comment:

  1. Sergio Marquina15 April 2021 at 23:01

    I can’t do my chores without taking a nap. The Compression Pump solved my problems. No pain in legs anymore.

    ReplyDelete

Subscribe to: Post Comments (Atom)