Oracle 11g Data pump: compression feature

May 17, 2011

—

by

Oracle - Oracle 11g Data pump: compression feature Last week, I worked on schema refresh in 11g database from one box to another box using data pump. There are two methods to achieve this. We will see them in this article.

Schema size: around 90 GB.
Version: 11.1.0.7

Method 1

Normally we follow below steps: (I have sufficient space in file system, so I am using below steps)
1.Export the schema using expdp/exp utility.
2.Compress the dump file.
3.Transfer the data through ftp to another box.
4.Just import it.

Step 2, depend upon dump size.

Note:

I have sufficient space in file system, so I am using method 1.

Method 2

Some stuff people use below method:

1.Export the schema using expdp/exp utility (during export they used mknod & compress).
2.Transfer the data through ftp to another box.
3.Just import it.

Refer: mknod

https://www.primeinspiration.com/programming/database/oracle/84-using-mknod-during-export.html

Someone thing, we know very well this concept. What he try to say?.
Dont stop. Please go ahead… 🙂

While we compress the dumpfile using OS-level utilities, we faced CPU utilization issue & also involved two steps (export the data & compress the dump file).

In oracle 11g, there is no need to use OS-level compress utility.

Hello Is there anybody wakeup?
Please big clap to oracle 11g.

COMPRESSION —> Reduce size of dumpfile contents valid keyword.
Values are: ALL, (METADATA_ONLY), DATA_ONLY and NONE.

Without compression option:

Export: Release 11.1.0.7.0 – Production on Friday, 14 August, 2009 9:36:53
Copyright (c) 2003, 2007, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “RAJA”.”SYS_EXPORT_SCHEMA_01″: raja/********@troy dumpfile=exp_raja.dmp logfile=exp_raja.log directory=exp_dir schemas=(‘RAJA’)
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 54 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/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/POST_SCHEMA/PROCACT_SCHEMA
. . exported “RAJA”.”OBJECTS” 6.864 MB 68693 rows
. . exported “RAJA”.”OBJECTS1″ 6.864 MB 68694 rows
. . exported “RAJA”.”OBJECTS2″ 6.864 MB 68695 rows
. . exported “RAJA”.”OBJECTS3″ 6.864 MB 68696 rows
. . exported “RAJA”.”OBJECTS4″ 6.864 MB 68697 rows
. . exported “RAJA”.”OBJECTS5″ 6.864 MB 68698 rows
Master table “RAJA”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for RAJA.SYS_EXPORT_SCHEMA_01 is:
E:APPBKUPEXP_RAJA.DMP
Job “RAJA”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 09:37:52

Dump size: 42,404 KB
After OS level zip: 4095 KB

With compression option

;;;
Export: Release 11.1.0.7.0 – Production on Saturday, 15 August, 2009 5:30:26

Copyright (c) 2003, 2007, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “RAJA”.”SYS_EXPORT_SCHEMA_01″: raja/********@troy dumpfile=exp_raja_comp.dmp logfile=exp_raja_comp.log directory=exp_dir schemas=(‘RAJA’) compression=all
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 54 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/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/POST_SCHEMA/PROCACT_SCHEMA
. . exported “RAJA”.”OBJECTS” 787.9 KB 68693 rows
. . exported “RAJA”.”OBJECTS1″ 788.2 KB 68694 rows
. . exported “RAJA”.”OBJECTS2″ 788.1 KB 68695 rows
. . exported “RAJA”.”OBJECTS3″ 788.2 KB 68696 rows
. . exported “RAJA”.”OBJECTS4″ 788.2 KB 68697 rows
. . exported “RAJA”.”OBJECTS5″ 788.2 KB 68698 rows
Master table “RAJA”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for RAJA.SYS_EXPORT_SCHEMA_01 is:
E:APPBKUPEXP_RAJA_COMP.DMP
Job “RAJA”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 05:31:21

Dump size: 4808 KB

Summary

Without compression dumpfile size: 42,404 KB
With compression dumpfile size : 4808 KB

Note:

There is no difference during import using compressed dumpfile/uncompressed dumpfile.

I Hope this article helped to you. Suggestions are welcome.

Tags:

Edward Ramamoorthy

I work for one of the top 10 tech companies in India. In my spare time, I write for PrimeInspiration.com.

Help Us Grow

If you like this post, please share it with your friends.

You are free to copy and redistribute this article in any medium or format, as long as you keep the links in the article or provide a link back to this page.