Oracle Utilities Tips
The export utility (exp) enables Remote DBAs to extract information
from the database. Exports play a vital role in a backup and
recovery strategy, and are a convenient way to duplicate
production data in test environments.
They are used for many reasons, including:
Logical database backups.
Copying data from one database to another (used in
combination with import).
Reorganization of segments (utilizing compress=y).
The export utility extracts objects (along with their
dependent objects) and their data from the database and writes
the data to an export file. This file is in binary format and
can reside either on disk or tape.
Exporting to disk is much faster, but may not be an option for
large databases where free disk space is limited. The export
files are only used by the import utility to load the data
into a different database or back into the same one. The
version of the import utility cannot be older than the export
There are four data export modes:
Full exports the contents of the entire database. This can
be time consuming and requires substantial disk space,
depending on the size of the database.
User exports all objects within a particular schema. If
user SCOTT performs a user mode export, all of the objects
belonging to SCOTT will be exported.
Table exports the DDL and data (optional) of any listed
tables, table partitions, or subpartitions. If the table
specified is partitioned, then all of its partitions will be
exported. Table names can be specified using wildcard
characters. In the example below, all of user DAVEs tables
will be exported, along with any table owned by SCOTT that
contains a D, and any table owned by AARON that contains an
S in the table name.
Tablespace exports all the tables residing in the
specified tablespace(s). All indexes on the corresponding
tables will also be exported, regardless of their tablespace
(which should always be a different one than the table). If a
table has one partition stored in the specified tablespace,
the entire table (all partitions) will be exported. This
option requires the exp_full_database role.
Each export mode addresses a different requirement and should
be used appropriately. For instance, full exports should not
be performed when only a few schemas have the data that needs
to be exported.
To learn more about these techniques, see
the book "Advanced
Oracle Utilities: The Definitive Reference".
You can buy it directly from the
publisher and get instant access to the code depot
of utilities scripts.