Overview of Oracle Data Pump

Oracle Data Pump technology enables very high-speed movement of data and metadata from one database to another. 

Data Pump Components 

Oracle Data Pump is made up of three distinct parts:

• The command-line clients,expdp and impdp
• The DBMS_DATAPUMP PL/SQL package (also known as the Data Pump API)
• The DBMS_METADATA PL/SQL package (also known as the Metadata API)

The expdp and impdp clients use the procedures provided in the DBMS_DATAPUMP PL/SQL package to execute export and import commands, using the parameters entered at the command line.

Note: All Data Pump Export and Import processing, including the reading and writing of dump files, is done on the system(server) selected by the specified database connect string. This means that, for nonprivileged users, the database administrator (DBA) must create directory objects for the Data Pump files that are read and written on that server filesystem. For privileged users, a default directory object is available.

Data Pum Export Options

Oracle provides the OS utility expdp that serves as the interface to Data Pump. Some of the options will be familiar if you have previous experience with the Export utility. However, some significant features are available only via Data Pump. Table 1-1 shows the command-line input parameters for expdp when creating a job. These parameters can be specified in a parameter file unless otherwise noted.

When you submit a job, Oracle will give the job a system-generated name. If you specify a name for the job via the JOB_NAME parameter, you must be certain that the job name will not conflict with the name of any table or view in your schema. During Data Pump jobs, Oracle will create and maintain a master table for the duration of the job. The master table will have the same name as the Data Pump job, so its name cannot conflict with existing objects. While a job is running, you can execute the commands in Table 1-2 via Data Pump’s interface.

Starting a Data Pump Export Job

Creating a Directory

Data Pump requires you to create directories for the datafiles and log files it will create and read. Use the CREATE DIRECTORY command to create the directory pointer within Oracle to the external directory you will use. Users who will access the Data Pump files must have the READ and WRITE privileges on the directory. Before you start, verify that the external directory exists and that the user who will be issuing the CREATE DIRECTORY command has the CREATE ANY DIRECTORY system privilege.

NOTE: In a default installation of Oracle Database 12c, a directory object called DATA_PUMP_DIR is created and points to the directory $ORACLE_BASE/admin/database_name/dpdump in a non-multitenant environment.

The following example creates a directory object called DPXFER in the Oracle instance dw referencing the file system directory /u01/app/oracle/DataPumpXfer and grants READ and WRITE access to the user RJB:

SQL> create directory dpxfer as '/u01/app/oracle/DataPumpXfer';

Directory created.

SQL> grant read, write on directory dpxfer to rjb;

Grant succeeded.

SQL>

The RJB user can now use the DPXFER directory for Data Pump jobs. The file system directory /u01/app/oracle/DataPumpXfer can exist on the source server, the target server, or any server on the network, as long as each server can access the directory and the permissions on the directory allow read/write access by the oracle user (the user that owns the Oracle executable files).On the server oc1, the administrator creates a directory with the same name that references the same network file system, except that privileges on the directory are granted to the HR user instead:

SQL> create directory dpxfer as '/u01/app/oracle/DataPumpXfer';

Directory created.

SQL> grant read,write on directory dpxfer to hr;

Grant succeeded.

SQL>

You can store your job parameters in a parameter file, referenced via the PARFILE parameter of expdp. 

For example, you can create a file named dp_rjb.par with the following entries:

directory=dpxfer

dumpfile=metadata_only.dmp

content=metadata_only

The logical Data Pump directory is DPXFER, the one created earlier in the chapter. The Data Pump Export will only have metadata; the name of the dump file, metadata_only.dmp, reflects the contents of the dump file. Here’s how you initiate a Data Pump job using this parameter file:

expdp rjb/rjb parfile=dp_rjb.par

Oracle will then pass the dp_rjb.par entries to the Data Pump Export job. A schema-type Data Pump Export (which is the default) will be executed, and the output (metadata only, no table rows) will be written to a file in the DPXFER directory. Here is the output from the expdp command:

[oracle@dw ~]$ expdp rjb/rjb parfile=dp_rjb.par

Export: Release 12.1.0.2.0 - Production on Thu Nov 13 09:13:10 2014

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 

64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

Starting "RJB"."SYS_EXPORT_SCHEMA_01": rjb/******** parfile=dp_rjb.par

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Master table "RJB"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for RJB.SYS_EXPORT_SCHEMA_01 is:

/u01/app/oracle/DataPumpXfer/metadata_only.dmp

Job "RJB"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Nov 13 09:13:50 

2014 elapsed 0 00:00:27 

[oracle@dw ~]$

HELP : Default:N.

Displays online help for the Export utility.

HELP = {y | n}

> expdp HELP = y