Exporting and Importing with Data Pump Export and Data Pump Import

The Data Pump Export utility exports data and metadata into a set of operating system files called a dump file set. The Data Pump Import utility imports an export dump file set into a target Oracle database.

A dump file set is made up of one or more disk files that contain table data, database object metadata, and control information. The files are written in a proprietary, binary format, which means that the dump file set can be imported only by the Data Pump Import utility. The dump file set can be imported to the same database or it can be moved to another system and loaded into the Oracle database there.

Because the dump files are written by the database, rather than by the Data Pump client application, you must create directory objects for the directories to which files will be written. A directory object is a database object that is an alias for a directory in the host operating system's file system.

Data Pump Export and Import enable you to move a subset of the data and metadata. This is done by using Data Pump parameters to specify export and import modes, as well as various filtering criteria.

You can also perform exports and imports over a network. In a network export, the data from the source database instance is written to a dump file set on the connected database instance. In a network import, a target database is loaded directly from a source database with no intervening dump files. This allows export and import operations to run concurrently, minimizing total elapsed time.

Data Pump Export and Import also provide a set of interactive commands so that you can monitor and modify ongoing export and import jobs.

Note:

Data Pump Export and Data Pump Import do not support XMLType data. If you need to export and import XMLType data, use the Export and Import options described in "Exporting and Importing Data with the Export and Import Utilities".

Example: Using Data Pump Export and Data Pump Import

In this example, suppose that you want to make some changes to the HR sample schema and then test those changes without affecting the current HR schema. You could export the HR schema and then import it into a new HRDEV schema, where you could perform development work and conduct testing. To do this, take the following steps:

Do one of the following:
- On Windows: Log in to the Oracle Database XE host computer as the user who installed Oracle Database XE, and then open a command window.
- On Linux: Start a terminal session and log in to the Oracle Database XE host computer with the oracle user account.
On Linux, ensure that environment variables are set according to the instructions in "Setting Environment Variables on the Linux Platform".
At the command prompt, issue the command appropriate to your operating system, to create the directory where the exported files will be placed:

On Windows:
```
MKDIR c:\oraclexe\app\tmp
```
On Linux:
```
mkdir /usr/lib/oracle/xe/tmp
```
Start SQL Command Line (SQL*Plus) and connect as user SYSTEM by entering the following at the command prompt:
```
sqlplus SYSTEM/password
```
where password is the password that you specified for the SYS and SYSTEM user accounts upon installation (Windows) or configuration (Linux) of Oracle Database XE.

At the SQL prompt, enter the following commands to create a directory object named dmpdir for the tmp directory that you just created, and to grant read and write access to it for user HR.

On Windows:

CREATE OR REPLACE DIRECTORY dmpdir AS 'c:\oraclexe\app\tmp';
GRANT READ,WRITE ON DIRECTORY dmpdir TO hr;

On Linux:

CREATE OR REPLACE DIRECTORY dmpdir AS '/usr/lib/oracle/xe/tmp';
GRANT READ,WRITE ON DIRECTORY dmpdir TO hr;

Export the HR schema to a dump file named schema.dmp by issuing the following command at the system command prompt:

expdp SYSTEM/password SCHEMAS=hr DIRECTORY=dmpdir DUMPFILE=schema.dmp LOGFILE=expschema.log

where password is the password for the SYSTEM user.

As the export operation takes place, messages similar to the following are displayed:

Export: Release 10.2.0.1.0 - Production on Tuesday, 13 December, 2005 11:48:01
 
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  SYSTEM/******** SCHEMAS=hr
  DIRECTORY=dmpdir DUMPFILE=schema.dmp LOGFILE=expschema.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
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/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."COUNTRIES"                            6.093 KB      25 rows
. . exported "HR"."DEPARTMENTS"                          6.640 KB      27 rows
. . exported "HR"."EMPLOYEES"                            15.77 KB     107 rows
. . exported "HR"."JOBS"                                 6.609 KB      19 rows
. . exported "HR"."JOB_HISTORY"                          6.585 KB      10 rows
. . exported "HR"."LOCATIONS"                            7.710 KB      23 rows
. . exported "HR"."REGIONS"                              5.296 KB       4 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  C:\ORACLEXE\APP\TMP\SCHEMA.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:48:46

The schema.dmp file and the expschema.log file are written to the dmpdir directory.

Import the dump file, schema.dmp, into another schema, in this case, HRDEV. You use the REMAP_SCHEMA command parameter to indicate that objects are to be imported into a schema other than their original schema. Because the HRDEV user account does not already exist, the import process automatically creates it. In this example, you will import everything except constraints, ref_constraints, and indexes. If a table already exists, it is replaced with the table in the export file.

At the operating system command prompt, issue the following command:

impdp SYSTEM/password SCHEMAS=hr DIRECTORY=dmpdir DUMPFILE=schema.dmp
  REMAP_SCHEMA=hr:hrdev EXCLUDE=constraint, ref_constraint, index
  TABLE_EXISTS_ACTION=replace LOGFILE=impschema.log

where password is the password for the SYSTEM user.

As the import operation takes place, messages similar to the following are displayed (this output is also written to the impschema.log file in the dmpdir directory):

Import: Release 10.2.0.1.0 - Production on Tuesday, 13 December, 2005 11:49:29
 
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  SYSTEM/******** SCHEMAS=hr
  DIRECTORY=dmpdir DUMPFILE=schema.dmp REMAP_SCHEMA=hr:hrdev
  EXCLUDE=constraint, ref_constraint, index TABLE_EXISTS_ACTION=replace LOGFILE=impschema.log 
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HRDEV"."COUNTRIES"                         6.093 KB      25 rows
. . imported "HRDEV"."DEPARTMENTS"                       6.640 KB      27 rows
. . imported "HRDEV"."EMPLOYEES"                         15.77 KB     107 rows
. . imported "HRDEV"."JOBS"                              6.609 KB      19 rows
. . imported "HRDEV"."JOB_HISTORY"                       6.585 KB      10 rows
. . imported "HRDEV"."LOCATIONS"                         7.710 KB      23 rows
. . imported "HRDEV"."REGIONS"                           5.296 KB       4 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 11:49:49

The HRDEV schema is now populated with data from the HR schema.

Assign a password to the newly created HRDEV user account. To do so, start SQL Command Line and connect as user SYSTEM (as you did in step 4), and then at the SQL prompt, enter the following ALTER USER statement:
```
ALTER USER hrdev IDENTIFIED BY hrdev;
```
This statement assigns the password hrdev.

You can now work in the HRDEV schema without affecting your production data in the HR schema.