ORACLE DATA PUMP Part #1

Oracle Data Pump is the replacement for the original Export and Import utilities. Available starting in Oracle Database 10g, Oracle Data Pump enables very high-speed movement of data and metadata from one database to another. The Data Pump Export and Import utilities have a similar look and feel to theoriginal utilities, but they are much more efficient and give you greater control and management of import and export jobs.

There are two new concepts in Oracle Data Pump that are different from original Export and Import.

1. Directory Objects: Data Pump differs from original Export and Import in that all jobs run primarily on the server using server processes. These server processes access files for the Data Pump jobs using directory objects that identify the location of the files. The directory objects enforce a security model that can be used by DBAs to control access to these files.

2. Interactive Command-Line Mode: Besides regular operating system command-line mode, there is now a very powerful interactive command-line mode which allows the user to monitor and control Data Pump Export and Import operations.

Creating Directory Objects

In order to use Data Pump, the database administrator must create a directory object and grant privileges to the user on that directory object. If a directory object is not specified, a default directory object called data_pump_dir is provided. The default data_pump_dir is available only to privileged users unless access is granted by the DBA.

The following SQL statement creates a directory object named dump_dir that is mapped to a directory located at /u01/dumps.

>sulplus / as sysdba
SQL> CREATE DIRECTORY dump_dir AS ‘/u01/dumps/’;

After a directory is created, you need to grant READ and WRITE permission on the directory to other users. For example, to allow the Oracle database to read and to write to files on behalf of user scott in the directory named by dump_dir, you must execute the following command:

SQL> GRANT READ,WRITE ON DIRECTORY dump_dir TO scott;

Note that READ or WRITE permission to a directory object means only that the Oracle database will read or write that file on your behalf. You are not given direct access to those files outside of the Oracle database unless you have the appropriate operating system privileges. Similarly, the Oracle database requires permission from the operating system to read and write files in the directories. Once the directory access is granted, the user scott can export his database objects with command arguments:

>expdp username/password DIRECTORY=dpump_dir1 dumpfile=scott.dmp

Comparison of command-line parameters from Original Export and Import to Data Pump

Data Pump commands have a similar look and feel to the original Export and Import commands, but are different. Below are a few examples that demonstrate some of these differences.

Import of tables from scott’s account to jim’s account

Original Import:
> imp username/password FILE=scott.dmp FROMUSER=scott TOUSER=jim TABLES=(*)
Data Pump Import:
> impdp username/password DIRECTORY=dpump_dir1 DUMPFILE=scott.dmp TABLES=scott.emp REMAP_SCHEMA=scott:jim

Continued in ORACLE DATA PUMP Part #2