This section provides the following examples of using Data Pump Export:
For information that will help you to successfully use these examples, see "Using the Export Parameter Examples".
Performing a Table-Mode Export
Example 2-1 shows a table-mode export, specified using the
TABLES parameter. Issue the following Data Pump export command to perform a table export of the tables
jobs from the human resources (
Example 2-1 Performing a Table-Mode Export
expdp hr TABLES=employees,jobs DUMPFILE=dpump_dir1:table.dmp NOLOGFILE=y
hr is exporting tables in his own schema, it is not necessary to specify the schema name for the tables. The
NOLOGFILE=y parameter indicates that an Export log file of the operation will not be generated.
Data-Only Unload of Selected Tables and Rows
Example 2-2 shows the contents of a parameter file (
exp.par) that you could use to perform a data-only unload of all tables in the human resources (
hr) schema except for the tables
regions. Rows in the
employees table are unloaded that have a
department_id other than 50. The rows are ordered by
Example 2-2 Data-Only Unload of Selected Tables and Rows
EXCLUDE=TABLE:"IN ('COUNTRIES', 'REGIONS')"
QUERY=employees:"WHERE department_id !=50 ORDER BY employee_id"
You can issue the following command to execute the
exp.par parameter file:
> expdp hr PARFILE=exp.par
A schema-mode export (the default mode) is performed, but the
CONTENT parameter effectively limits the export to an unload of just the table's data. The DBA previously created the directory object
dpump_dir1 which points to the directory on the server where user
hr is authorized to read and write export dump files. The dump file
dataonly.dmp is created in
Estimating Disk Space Needed in a Table-Mode Export
Example 2-3 shows the use of the
parameter to estimate the space that would be consumed in a table-mode
export, without actually performing the export operation. Issue the
following command to use the
BLOCKS method to estimate the
number of bytes required to export the data in the following three
tables located in the human resource (
Example 2-3 Estimating Disk Space Needed in a Table-Mode Export
> expdp hr DIRECTORY=dpump_dir1 ESTIMATE_ONLY=y TABLES=employees,
departments, locations LOGFILE=estimate.log
The estimate is printed in the log file and displayed on the client's
standard output device. The estimate is for table row data only; it
does not include metadata.
Performing a Schema-Mode Export
Example 2-4 shows a schema-mode export of the
schema. In a schema-mode export, only objects belonging to the
corresponding schemas are unloaded. Because schema mode is the default
mode, it is not necessary to specify the
SCHEMAS parameter on the command line, unless you are specifying more than one schema or a schema other than your own.
Example 2-4 Performing a Schema Mode Export
> expdp hr DUMPFILE=dpump_dir1:expschema.dmp LOGFILE=dpump_dir1:expschema.log
Performing a Parallel Full Database Export
Example 2-5 shows a full database Export that will have up to 3 parallel processes (worker or PQ slaves).
Example 2-5 Parallel Full Export
> expdp hr FULL=y DUMPFILE=dpump_dir1:full1%U.dmp, dpump_dir2:full2%U.dmp
FILESIZE=2G PARALLEL=3 LOGFILE=dpump_dir1:expfull.log JOB_NAME=expfull
Because this is a full database export, all data and metadata in the database will be exported. Dump files
dmp, and so on will be created in a round-robin fashion in the directories pointed to by the
directory objects. For best performance, these should be on separate
I/O channels. Each file will be up to 2 gigabytes in size, as necessary.
Initially, up to three files will be created. More files will be
created, if needed. The job and master table will have a name of
expfull. The log file will be written to
expfull.log in the
Using Interactive Mode to Stop and Reattach to a Job
To start this example, reexecute the parallel full export in Example 2-5.
While the export is running, press Ctrl+C. This will start the
interactive-command interface of Data Pump Export. In the interactive
interface, logging to the terminal stops and the Export prompt is
Example 2-6 Stopping and Reattaching to a Job
At the Export prompt, issue the following command to stop the job:
Are you sure you wish to stop this job ([y]/n): y
The job is placed in a stopped state and exits the client.
Enter the following command to reattach to the job you just stopped:
> expdp hr ATTACH=EXPFULL
After the job status is displayed, you can issue the
CONTINUE_CLIENT command to resume logging mode and restart the
A message is displayed that the job has been reopened, and processing status is output to the client.