Summary: in this tutorial, you will learn how to use the Oracle Data Pump Export utility to unloading data (and metadata) from the Oracle database.
Introduction to Oracle Data Pump Export tool
Oracle Data Pump Export is a built-in utility program for unloading data and metadata into a set of dump files. The dump file set then can be imported by the Data Pump Import utility on the same or another Oracle Database system.
The dump file set contains table data, database object metadata, and control information that are written in an Oracle-proprietary, binary format.
The Data Pump Export works on the server only, therefore, it typically deals with directory object that maps to physical directories on the database server. The Export Data Pump tool does not write to the local file system on your client computer.
Notice that Oracle introduced the Data Pump Export utility starting in Oracle 10g. The Data pump Export is a replacement of the old Export utility. According to Oracle, the new Data Pump Export can be up to 40 times faster.
Here are some notable features of the Oracle Data Pump Export tool:
- Compression of output files
- Export via network link
- Using a subquery to export partial data.
- Renaming tables/schemas/tablespaces
Calling Data Pump Export program
You invoke the Data Pump Export program using the
expdp command. The behaviors of the program are determined by the parameters specified either on the command line or in a parameter file.
Oracle Data Pump Export example
First, create a new directory object
ot_external that maps to the
CREATE DIRECTORY ot_external AS 'C:\export';
Second, create a parameter file named
customer.par with the following contents and place the file in the
userid=ot@pdborcl/Abcd1234 directory=ot_external dumpfile=customer_exp%U.dmp logfile=customer_exp.log filesize=50K tables=customers
In this parameter file:
- The first line specifies the user and password (
userid) for connecting to the Oracle database.
- The second line indicates the directory object which maps to the output directory that stores the dump file set.
- The dump files will have names
customer_exp2.dmp, … The sequence number is generated based on the
- The log file will be
- Each dump file will have a maximum size of 40KB, just for demonstration purpose. If a dump file has the size exceeds 40K, the Data Pump Export tool will create the next dump file. The valid range of the dump files is from 40K to 16TB.
- The Data Pump Export will export only the
customerstable specified by the last line in the parameter file
Third, invoke the Data Pump Export program to export the
customers table to the dump files:
Here is the dump file set:
Now, it is your turn to export all objects in the
OT schema to the dump files by creating a new parameter file with the following contents:
userid=ot@pdborcl/Abcd1234 directory=ot_external dumpfile=ot_exp%U.dmp logfile=ot_exp.log filesize=50K schemas=ot
And run this command:
In this tutorial, you’ve learned how to use the Oracle Data Pump Export utility program to unload data and metadata from the Oracle database.