Database Reference
In-Depth Information
When you use the
SQLFILE
option with an import, the
impdp
process doesn't import any data; it only creates a file
that contains the SQL commands that would be run by the import process. It's sometimes handy to generate an SQL
file for the following reasons:
•
Preview and verify the SQL statements before running the import
•
Run the SQL manually to precreate database objects
•
Capture the SQL that would be required to recreate database objects (users, tables, index, and
so on)
In regard to the last bulleted item, sometimes what's checked into the source code control repository doesn't
match what's really been applied to the production database. This procedure can be handy for troubleshooting or
documenting the state of the database at a point in time.
Cloning a User
Suppose you need to move a user's objects and data to a new database. As part of the migration, you want to rename
the user. First, create a schema-level export file that contains the user you want to clone. In this example the user
name is
INV
:
$ expdp mv_maint/foo directory=dp_dir schemas=inv dumpfile=inv.dmp
Now, you can use Data Pump import to clone the user. If you want to move the user to a different database, copy
the dump file to the remote database, and use the
REMAP_SCHEMA
parameter to create a copy of a user. In this example
the
INV
user is cloned to the
INV_DW
user:
$ impdp mv_maint/foo directory=dp_dir remap_schema=inv:inv_dw dumpfile=inv.dmp
This command copies all structures and data in the
INV
user to the
INV_DW
user. The resulting
INV_DW
user is
identical, in terms of objects, to the
INV
user. The duplicated schema also contains the same password as the schema
from which it was copied.
If you just want to duplicate the metadata from one schema to another, use the
CONTENT
parameter with the
METADATA_ONLY
option:
$ impdp mv_maint/foo directory=dp_dir remap_schema=inv:inv_dw \
content=metadata_only dumpfile=inv.dmp
The
REMAP_SCHEMA
parameter provides an efficient way to duplicate a schema, with or without the data. During a
schema duplication operation, if you want to change the tablespace in which the objects reside, also use the
REMAP_TABLESPACE
parameter. This allows you to duplicate a schema and also place the objects in a tablespace
different from that of the source objects.
You can also duplicate a user from one database to another without first creating a dump file. To do this, use
the
NETWORK_LINK
parameter. See the section “Exporting and Importing Directly Across the Network,” earlier in this
chapter, for details on copying data directly from one database to another.
