Wednesday, September 21, 2011

COMPRESSION and CONTENT parameter in Data pump.


COMPRESSION:

COMPRESSION parameter is used to compress our export dumpfile.This is different to prior OS-level utilities where we faced CPU utilization issue & also involved two steps (export the data & compress the dump file).in order to accomplish this task.

It specifies which data to compress before writing to the dump file set.

Options:

COMPRESSION={ALL | DATA_ONLY | METADATA_ONLY | NONE}

ALL enables compression for the entire export operation.

DATA_ONLY results in all data being written to the dump file in compressed format.

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.

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


Example: expdp scott/tiger@delhi dumpfile=exp_scott_comp.dmp logfile=exp_scott_comp.log directory=exp_dir schemas=SCOTT compression=all

CONTENT:

It is used to filter the export in three forms i.e.data only, metadata only, or both.

CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

ALL unloads both data and metadata. This is the default.

DATA_ONLY unloads only table row data; no database object definitions are unloaded.

METADATA_ONLY unloads only database object definitions; no table row data is unloaded.

Example:

Export: Release 11.2.0.1.0 - Production on Wed Sep 21 11:27:35 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

c:\>expdp system/sys@delhi schemas=scott dumpfile=hh.dmp content=METADATA_ONLY

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 "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@delhi schemas=scott dumpfile=hh.dmp content=METADATA_ONLY
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/CONSTRAINT/REF_CONSTRAINT
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  D:\APP\BISHWANATH\ADMIN\DELHI\DPDUMP\HH.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:28:27

Enjoy:-)