Databases Reference
In-Depth Information
Compressing Output
When you use Data Pump to create large files, you should consider compressing the output. As of Oracle Database
11g, the
COMPRESSION
parameter can be one of the following values:
ALL
,
DATA_ONLY
,
METADATA_ONLY
, or
NONE
. If
you specify
ALL
, then both data and metadata are compressed in the output. This example exports one table and
compresses both the data and metadata in the output file:
$ expdp dbauser/foo tables=locations directory=datapump \
dumpfile=compress.dmp compression=all
If you're using Oracle Database 10g, then the
COMPRESSION
parameter only has the
METADATA_ONLY
and
NONE
values.
■
the
ALL
and
DATA_ONLY
options of the
COMPRESS
parameter require a license for the Oracle advanced
Compression option.
Note
New with Oracle Database 12c, you can specify a compression algorithm. The choices are
BASIC
,
LOW
,
MEDIUM
, and
HIGH
. Here is an example of using
MEDIUM
compression:
$ expdp mv_maint/foo dumpfile=full.dmp directory=dp_dir full=y \
compression=all compression_algorithm=MEDIUM
Using the
COMPRESSION_ALGORITHM
parameter can be especially useful if you're running low on disk space or
exporting over a network connection (as it reduces the number of bytes that need to be transferred).
■
Note
the
COMPRESSION_ALGORITHM
parameter requires a license for the Oracle advanced Compression option.
Changing Table Compression Characteristics on Import
Starting with Oracle Database 12c, you can change a table's compression characteristics when importing the table.
This example changes the compression characteristics for all tables imported in the job to
ROW STORE COMPRESS
ADVANCED
. Because the command in this example requires quotation marks, it's placed in a parameter file, as shown:
userid=mv_maint/foo
dumpfile=inv.dmp
directory=dp_dir
transform=table_compression_clause:"ROW STORE COMPRESS ADVANCED"
Assume that the parameter file is named
imp.par
. It can now be invoked as follows:
$ impdp parfile=imp.par
All tables included in the import job are created as
ROW STORE COMPRESS ADVANCED
, and the data are
compressed as they're loaded.
■
Note
table-level compression requires a license for the Oracle advanced Compression option.