Database Reference
In-Depth Information
This code listing depends on a database directory object's being created that points to where you want the daily
dump file to be written. You may also want to set up another job that periodically deletes any files older than a certain
amount of time.
Compressing Output
When you use Data Pump to create large files, you should consider compressing the output. As of Oracle 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 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 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 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
COMPRESS FOR OLTP
. 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:"COMPRESS FOR OLTP"
Assume that the parameter file is named
imp.par
. It can now be invoked as follows:
$ impdp parfile=imp.par