Oracle 11g Data pump: compression feature

Submitted by rajabaskar on Sat, 2009-09-05 10:57

Technical Articles

Last week, I worked on schema refresh in 11g database from one box to another box using data pump.

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:

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: about mknod

http://dbarajabaskar.blogspot.com/2008/08/using-mknod-during-export.html

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.

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:\APP\BKUP\EXP_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:\APP\BKUP\EXP_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.

Best Regards
RajaBaskar Thangaraj
www.dbarajabaskar.blogspot.com
Mail me @ rajabaskar.t@gmail .com

rajabaskar's blog
Login to post comments

Thank you Rajabaskar

Submitted by chandu_imax on Thu, 2009-09-10 05:35.

Hi Raja,

This article will be useful for all. Thank you.
But, when I tried with the below query, I got stuck with the below error.

exp username/password@db_name file=tables.dmp log=logfile.log tables=table1,table2,table3 compression=all

LRM-00101: unknown parameter name 'compression'

EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully

Regards,
Chandu.

Compression parameter

Submitted by sqweeks on Thu, 2009-09-17 06:47.

Chandu,

The compression parameter is used by datapump export (expdp), not by original export (exp). Thus, the error you received "unknown parameter".

Your example was using exp. There is a parameter compress used by exp, but if used by exp tells the import to "import into one extent". The default is Y.

You've likely already resolved this.

Regards,
Susan

Hi chandu, Compression

Submitted by rajabaskar on Mon, 2009-09-21 10:54.

Hi Chandu,

The compression feature is available with Oracle 11g datapump and not with export/import.

Thank you.

Regards.
Rajabaskar Thangaraj

regarding compression=all

Submitted by karankukreja on Wed, 2010-07-14 00:00.

Hello,

I have gone through your blog regarding the using of EXPDP.
I wanted to know if we can use compression=all parameter directly, or do we need some separate license for that?

Because I've been reading a lot on EXPDP and I found out that:

1. We cannot pipe it directly to a 'zip' file like in the case of exp command.

2. We can first do compression using the compression parameter and then think of making a 'Zip' file out of it (I need to zip it irrespective of the size because Business demands that format).

Advanced Compression licensing

Submitted by Frank Naude on Mon, 2013-05-06 03:26.

As far as I understand Advanced Compression licenses are required for the ALL and DATA_ONLY options.

Also see Bug 8478082: DISALLOW UNLICENSED COMPRESSION FEATURE WHILE USING DATA PUMP

User login

Site navigation