Database Reference
In-Depth Information
Exporting and Importing an Entire Database
When you export an entire database, this is sometimes referred to as a full export. In this mode the resultant export
file contains everything required to make a copy of your database. Unless restricted by filtering parameters (see the
section “Filtering Data and Objects,” later in this chapter), a full export consists of
•
all DDL required to recreate tablespaces, users, user tables, indexes, constraints, triggers,
sequences, stored PL/SQL, and so on.
SYS
user's tables)
A full export is initiated with the
FULL
parameter set to
Y
and must be done with a user that has DBA privileges or
that has the
DATAPUMP_EXP_FULL_DATABASE
role granted to it. Here is an example of taking a full export of a database:
•
all table data (except the
$ expdp mv_maint/foo directory=dp_dir dumpfile=full.dmp logfile=full.log full=y
As the export is executing, you should see this text in the output, indicating that a full-level export is taking place:
Starting "MV_MAINT"."SYS_EXPORT_FULL_01":
Be aware that a full export doesn't export everything in the database:
SYS
schema are not exported (there are a few exceptions to this, such
as the
AUD$
table). Consider what would happen if you could export the contents of the
SYS
schema from one database and import them into another. The
SYS
schema contents would
overwrite internal data dictionary tables/views and thus corrupt the database. Therefore, Data
Pump never exports objects owned by
SYS
.
•
The contents of the
•
Index data are not exported, but rather, the index DDL that contains the SQL required to
recreate the indexes during a subsequent import.
Once you have a full export, you can use its contents to either recreate objects in the original database (e.g., in the
event a table is accidentally dropped) or replicate the entire database or subsets of users/tables to a different database.
This next example assumes that the dump file has been copied
to
a different database server and is now used to
import all objects into the destination database:
$ impdp mv_maint/foo directory=dp_dir dumpfile=full.dmp logfile=fullimp.log full=y
■
to initiate a full database import, you must have DBa privileges or be assigned the
DATAPUMP_IMP_FULL_
DATABASE
role.
Tip
In the output displayed on your screen, you should see an indication that a full import is transpiring:
Starting "MV_MAINT"."SYS_IMPORT_FULL_01":
Running a full-import database job has some implications to be aware of:
•
The import job will first attempt to recreate any tablespaces. If a tablespace already exists,
or if the directory path a tablespace depends on doesn't exist, then the tablespace creation
statements will fail, and the import job will move on to the next task.
SYS
and
SYSTEM
user accounts to contain the same password
that was exported. Therefore, after you import from a production system, it's prudent to
change the passwords for
SYS
and
SYSTEM
, to reflect the new environment.
•
Next, the import job will alter the
