Data Pump - Oracle RMAN for Absolute Beginners - page 228

Common Data Pump Tasks
The following sections describe common features you can use with Data Pump. Many of these features are standard
with Data Pump, such as creating a consistent export and taking action when imported objects already exist in the
database. Other features, such as compression and encryption, require the Enterprise Edition of Oracle or an extra
license, or both. I'll point out these requirements (if relevant) for the Data Pump element being covered.

Estimating the Size of Export Jobs
If you're about to export a large amount of data, you can estimate the size of the file that Data Pump creates before you
run the export. You may want to do this because you're concerned about the amount of space an export job needs.
To estimate the size, use the ESTIMATE_ONLY parameter. This example estimates the size of the export file for an
entire database:

$ expdp mv_maint/foo estimate_only=y full=y logfile=n

Here is a snippet of the output:

Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 6.75 GB

Similarly, you can specify a schema name to get an estimate of the size required to export a user:

$ expdp mv_maint/foo estimate_only=y schemas=star2 logfile=n

Here is an example of estimating the size required for two tables:

$ expdp mv_maint/foo estimate_only=y tables=star2.f_configs,star2.f_installations \
logfile=n

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