Oracle Data Pump

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

1.What Is Data Pump Export?

Data Pump Export is a utility for unloading data and metadata into a set of operating system

files called a dump file set. The dump file set can be imported only by the Data Pump Import 

utility. The dump file set can be imported on the same system or it can be moved to another 

system and loaded there.

Data Pump Export Modes

* Full Export Mode 

* Schema Mode 

* Table Mode 

* Tablespace Mode

Full Export Mode:-

A full export is specified using the FULL parameter. In a full database export, the entire database is unloaded. This mode requires that you have the EXP_FULL_DATABASE role.

Example:

The following is an example of using the FULL parameter. The dump file, expfull.dmp is written 

to the dpump_dir2 directory.

> expdp hr/hr DIRECTORY=dpump_dir2 DUMPFILE=expfull.dmp FULL=y NOLOGFILE=y

Schema Mode:-

A schema export is specified using the SCHEMAS parameter. This is the default export mode. 

If you have the EXP_FULL_DATABASE role, then you can specify a list of schemas and optionally 

include the schema definitions themselves, as well as system privilege grants to those schemas. 

If you do not have the EXP_FULL_DATABASE role, you can export only your own schema.

Example:

> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp SCHEMAS=hr,sh,oe 

Table Mode:-

A table export is specified using the TABLES parameter. In table mode, only a specified set of tables, 

partitions, and their dependent objects are unloaded. You must have the EXP_FULL_DATABASE role to specify tables that are not in your own schema. All specified tables must reside in a single schema.

Example:

The following example shows a simple use of the TABLES parameter to export three tables found 

in the hr schema: employees, jobs, and departments. Because user hr is exporting tables found 

in the hr schema, the schema name is not needed before the table names.

> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tables.dmp TABLES=employees,jobs,departments

Tablespace Mode:-

A tablespace export is specified using the TABLESPACES parameter.

Example:

> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tbs.dmp TABLESPACES=tbs_4, tbs_5, tbs_6

2.What Is Data Pump Import?

Data Pump Import is a utility for loading an export dump file set into a target system.

Data Pump Import Modes

*Full Import Mode

*Schema Mode

*Table Mode

*Tablespace Mode

Full Import Mode:-

> impdp hr/hr DUMPFILE=dpump_dir1:expfull.dmp FULL=y 

LOGFILE=dpump_dir2:full_imp.log

Schema Mode:-

> impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp LOGFILE=skip.log

SKIP_UNUSABLE_INDEXES=y

Table Mode:-

> impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp TABLES=employees,jobs

Tablespace Mode:-

> impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp TABLESPACES=tbs_1,tbs_2,tbs_3,tbs_4

Network Considerations

For Export

>expdp hr/hr@inst1 DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp TABLES=employees

For Import

> impdp hr/hr@inst1 DIRECTORY=dpump_dir DUMPFILE=hr.dmp TABLES=employees

Oracle Datapump parameter REMAP_SCHEMA

Loads all objects from the source schema into a target schema.

Syntax

REMAP_SCHEMA=source_schema:target_schema

Suppose that you execute the following Export and Import commands to remap the hr schema into the scott schema:

> expdp SYSTEM/password SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp


> impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmpREMAP_SCHEMA=hr:scott

In this example, if user scott already exists before the import, then the Import REMAP_SCHEMAcommand will add objects from the hr schema into the existing scott schema. You can connect to thescott schema after the import by using the existing password (without resetting it).

If user scott does not exist before you execute the import operation, Import automatically creates it with an unusable password. This is possible because the dump file, hr.dmp, was created by SYSTEM, which has the privileges necessary to create a dump file that contains the metadata needed to create a schema. However, you cannot connect to scott on completion of the import, unless you reset the password forscott on the target database after the import completes.