Data Pump - Pro Oracle Database 12c Administration - page 364

Listing the Contents of Dump Files
Data Pump has a very robust method of creating a file that contains all the SQL that's executed when an import job
runs. Data Pump uses the DBMS_METADATA package to create the DDL that you can use to recreate objects in the Data
Pump dump file.
Use the SQLFILE option of Data Pump import to list the contents of a Data Pump export file. This example creates
a file named expfull.sql , containing the SQL statements that the import process calls (the file is placed in the
directory defined by the DPUMP_DIR2 directory object):

$ impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp \
SQLFILE=dpump_dir2:expfull.sql

If you don't specify a separate directory (such as dpump_dir2 , in the previous example), then the SQL file is
written to the location specified in the DIRECTORY option.

■ You must run the previous command as a user with DBa privileges or the schema that performed the Data pump
export. Otherwise, you get an empty SQL file without the expected SQL statements in it.

Tip

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.