The Data Pump Export utility (expdp) introduced in Oracle 10g looks and feels a lot the exp utility. However, expdp comes with more features and is much more efficient than exp. This topic reviews the architectural specifics for the Data Pump Export program and examines the features of expdp not found in exp. Finally, some of these new features are demonstrated in a series of examples.

Overview of Data Pump Export

The Data Pump Export client, expdp, has much the same functionality as the exp program, plus a number of additional features, including:

  • The ability to estimate the size of the files that will result from the expdp operation, without actually having to write those files.
  • The ability to suspend and resume an export job at will.
  • The ability to attach or detach from a running export job at will.
  • The ability to restart many failed jobs from the point of failure.
  • The ability to do fine-grained object selection when exporting. Thus, you can choose to only export procedures and functions.
  • The ability to control the number of threads that will be used during the export operation.
  • The availability of two data access methods: external tables (taking advantage of the Oracle 10g features associated with external tables) or the direct path access method. The method used is selected automatically during the export process.
  • Support for network mode operations, which allow you to load from another database, through a database link, directly.
  • Control over the version of the object that is exported. This allows you to export data from one version of Oracle and ensure that it is compatible with a lower-level version. Note that this applies to versions of Oracle Database after 10g and that this feature is not available for any version earlier than Oracle Database 10g.
  • The ability to choose between three methods of database extraction: extract only database metadata (e.g., table and index creation statements); extract only data from the database; extract both database metadata and data at the same time.

Using Data Pump Export

There is some initial setup required before using the export tool.

Preparing to Use Data Pump Export

Before using expdp, a directory object should be created in the database that you will be exporting from. This is done with the create directory and grant commands, as shown in this example:

Directory created.

Grant succeeded.

The directory PUMP_DIR (which can be named anything you like) is created because all Data Pump related files are stored on the server, and not on the client. It is from this location that Oracle Data Pump will write/read all files. This provides a layer of security on the server, in that it limits reading or writing to specific locations. This prevents users from placing files where they do not belong and from reading files that they do not have a right to read.

When using expdp, the following order of operations is used to determine where the file will be written:

  1. Oracle writes to a directory specifically listed as a part of the dumpfile specification in the expdp command line.
  2. Oracle writes to the directory specified in the directory parameter setting in the expdp command line.
  3. Oracle writes to a location defined by the value of an environment variable (which is OS specific) on the client running the export that defines the default directory location. Also, if a user other than a SYSDBA user is going to use expdp to export anything other than the schema that is associated with their login, that user must be granted the privilege exp_full_database.

Data Pump Export: Parameters

Data Pump Export has a number of available parameters. The table below lists and describes each parameter. Examples of using many of these parameters are provided later in this topic.

attach Indicates that Data Pump Export should attach to an existing job that is already running.
content Allows you to control whether data or just database-related metadata is exported. Options: ALL, DATA_ONLY, and METADATA_ONLY.
directory Defines the directory object to be used for the export dump files and log files. A directory of the same name must be created in the database that you are exporting or an error will occur.
data_options (Oracle 11g) This parameter provides for special handling of certain types of data-related issues. See Oracle 11g Data Pump Enhancements for more information.
dumpfile Provides a list of destination dump files. Multiple dump files can be comma delimited. Also, a directory name can be included, separated from the filenames with a colon (:). Additionally, a substitution variable (%U) is available, which is a two-digit number from 01 to 99. This allows for the creation of multiple dump files. Note that expdp will not overwrite an existing file.

Tells the expdp process how to calculate the size of the resulting dump file. Options include:

  • BLOCKS - Calculate the dump file size based on the number of blocks of data times the database block size (default method).
  • SAMPLING - Calculate the size based on a sample of the number of rows per table.
  • STATISTICS - Base the size of the export on the current object statistics.
estimate_only Causes expdp to determine the estimated size of the job, without actually doing the export.
exclude Excludes certain metadata from the export operation. Note that for any object excluded, any dependent objects also are excluded. For example, exclusion of a table also excludes any indexes, triggers, constraints and the related table data. You can also use wildcard characters and SQL statements to exclude specific ranges of objects.
filesize Limits the size of the dump files being created. This parameter can be specified in bytes, or you can follow the size with the letter B (bytes), K (kilobytes), M (megabytes), or G (gigabytes).
flashback_scn Allows you to use the flashback features when exporting the database. In this case, expdp will use the stated SCN to flashback to.
flashback_time Allows you to use the flashback features when exporting the database. In this case, expdp will use the stated time to flashback to.
full If set to Y, this parameter indicates that expdp should export the entire database. The default value for this parameter is N, which does not do a full export.
help Allows you to display help messages and the syntax of expdp.
include Allows you to define specific objects that you want exported. Only those objects, and dependent objects, are exported.
job_name Defines the name of the export job. This name is used to manage the job (for example, via the attach command). By default, this name is system generated using the naming convention sys_operation_mode_nn. For example, a full export might take the job name sys_export_full_01.
logfile Identifies the log file that is generated during the export operation. By default, it's called export.log and stored in the location defined by the directory parameter.
network_link Allows for a network export through a remote database link to a predefined source system.
nologfile Suppresses the writing of the expdp log file. Set to N by default.
parallel Defines the maximum number of threads that can operate on behalf of the export job. This parameter allows you to adjust the level of parallelism, to provide a balance between system resource usage and time to create the export. This can be set when the export is started, and can also be changed via interactive mode. If the system resources allow, the number of parallel processes should be set to the number of dump files being created. The default for this setting is 1.
parfile Allows you to define an external parameter file for the expdp process. The parameter file is local to the client and does not use a database directory as do the export dump files or log files.
query Allows for the application of a SQL predicate to filter the database being exported. For example, this allows you to export from the STORE_SALES table, sales from store 100.
remap_data (Oracle 11g) Provides for the definition of one or more schema table.column object(s) to be remapped. See Oracle 11g Data Pump Enhancements for more information.
schemas Defines the schemas you wish to export data from. The user must have the exp_full_database privilege to export any schema other than the schema that they have logged expdp into. Any table in the SYS.NOEXP$ table will not be exported in schema export mode.
status Defines how frequently the job status should be updated, in seconds. Defaults to 0 seconds.
tables Allows you to export specific tables only. Dependent objects are exported also.
tablespaces Allows you to export objects listed in the specified tablespaces. All dependent objects in other tablespaces (e.g., indexes) also are exported.
transport_full_check During transportable tablespace operation, verifies that there are no dependencies between objects inside the transportable set and objects outside the transportable set.
transport_tablespaces Allows for the export of transportable tablespace metadata.
transportable (Oracle 11g) When the transportable parameter is used in impdp or expdp only the metadata associated with specific tables, partitions, or sub-partitions will be extracted, rather than all metadata. You can then proceed to transport the associated data files as you normally would. See Oracle 11g Data Pump Enhancements for more information.
version Restricts objects being exported to a specific version level of the database. This is designed to help with compatibility issues when moving database objects from a higher version of the database to a lower version of the database.

Interactive mode has several commands available when called by expdp, as described below.

add_file Add a file to the list of dump files
continue_client Return to client logging mode
exit_client Exit client logging mode without killing the job
help Provides help on all commands
kill_job Detach from and delete an existing job
parallel Change the number of active workers for current job
start_job Start or resume the current job
status Define the refresh frequency for monitoring a job in seconds (default is 0 seconds)
stop_job Stop job execution and exit the client

Data Pump Export: Pitfalls

There are a few pitfalls to be aware of with regard to Oracle Data Pump. When you run a Data Pump job, a temporary table is created in the schema that you log in to. This table is supposed to be cleaned up after the job ends. If the job ends abnormally (e.g., database crash in the middle of the job), then you may need to clean up the table. You will find the table in the same schema that you logged in to, and it will be named the same as the job_name parameter setting. Therefore, if you logged in using the SCOTT account and your job_name was set to FULL_EXPORT, the table will be in the SCOTT schema and the name of it will be FULL_EXPORT.

This leads to another little quirk: if you have a table with the same name as the JOB_NAME in the schema that you are logging into, the job will fail because the table already exists.

Data Pump Export: Examples

This section provides you with several examples of the use of Data Pump Export. The output is not included from most of these commands, only the text of the commands.

Example 1: A Full Database Export

This first example is quite basic. It's simply a dump of the entire database.

C:\Oracle\Oracle Database 10g-beta2\BIN>expdp scott/tiger
dumpfile=pump_dir:mydb_%U.dat filesize=100m
nologfile=y job_name=robert full=y

This example creates a full database export. Each file will be no more than 100MB in size, and all dump files and the log file will be stored in the PUMP_DIR library. The job is assigned a job name of Robert and will not create a log file.

Example 2: Export Tablespace Data

This example only exports data from a single tablespace, named USERS, with the following command:

C:\Oracle\Oracle Database 10g-beta2\BIN>expdp scott/tiger
nologfile=y job_name=tablespace tablespaces=users

Example 3: Exporting Database Data Only

This example only exports database data for the entire database. It also creates a log file, so the logfile parameter is included. It does not allow expdp to create the default log file because the default directory entry that expdp would be looking for, which is called DPUMP_DIR in the database, was not defined. Thus, the expdp process would have failed when trying to create the directory.

C:\Oracle\Oracle Database 10g-beta2\BIN>expdp scott/tiger
dumpfile=pump_dir:mydb_%U_data.dat filesize=100m
content=data_only job_name=data_extract full=y

Example 4: Exporting Specific Database Object Types

This example creates a parameter file that has individual include statements. This export will export all database functions, procedures, and any table that has a name that starts with EMP.

-- Parameter file

Along with the preceding parameter file, the following command line is used:

C:\Oracle\Oracle Database 10g-beta2\BIN>expdp scott/tiger

Example 5: Estimating the Size of the Export

It can be useful to know if there is enough space to do an export. Fortunately, Data Pump provides the capability to make that determination. Simply use the estimate_only clause to get the estimate of how big the dump file will be without the actual creation of the dump file. Here is an example:

C:\Oracle\Oracle Database 10g-beta2\BIN>expdp scott/tiger
full=y estimate_only=Y estimate=statistics nologfile=y

This example uses the database statistics as the base for our estimate. It is also possible to use the sampling method, which samples a fixed number of rows in each table and extrapolates from that number the total size of the backup. The default value for this parameter, BLOCKS, is the most accurate but also the most time consuming.

Note: In tests, the default method of calculating the size of the resulting dump file took a great deal longer to complete than using either of the alternative methods. This resulted in significantly longer export run times.