Oracle 10g Free Training - Using Data Pump Import

Oracle 10g Free Training - Using Data Pump Import

In this tutorial we shall learn about using Data Pump Import - Invoking Data Pump Import, Data Pump Import Interfaces, Data Pump Import Modes like Full Import Mode, Schema Mode, Table Mode, Tablespace Mode and Transportable Tablespace Mode.

Using Data Pump Import

Oracle export and import utilities enable you to move existing data in Oracle format between one Oracle Database and another. For example, export files can archive database data or move data among different databases that run on the same or different operating systems.

Data Pump Import (invoked with the impdp command) is a new utility as of Oracle Database 10g. Although its functionality and its parameters are similar to those of the original Import utility (imp), they are completely separate utilities and their files are not compatible.

Data Pump Import (hereinafter referred to as Import for ease of reading) is a utility for loading an export dump file set into a target system. The 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. During an import operation, the Data Pump Import utility uses these files to locate each database object in the dump file set.

Import can also be used to load a target database directly from a source database with no intervening dump files. This allows export and import operations to run concurrently, minimizing total elapsed time. This is known as a network import.

Data Pump Import enables you to specify whether a job should move a subset of the data and metadata from the dump file set or the source database (in the case of a network import), as determined by the import mode. This is done using data filters and metadata filters, which are implemented through Import commands.

Invoking Data Pump Import

The Data Pump Import utility is invoked using the impdp command from the command prompt or the OEM. The characteristics of the import operation are determined by the import parameters you specify. These parameters can be specified either on the command line or in a parameter file.

The figure below shows the OEM screen with which the Import/Export can be performed.

Figure. Import From Files

Data Pump Import Interfaces

You can interact with Data Pump Import by using a command line, a parameter file, or an interactive-command mode.

• Command-Line Interface: Enables you to specify the Import parameters directly on the command line. For a complete description of the parameters available in the command-line interface.
  .
• Parameter File Interface: Enables you to specify command-line parameters in a parameter file. The only exception is the PARFILE parameter because parameter files cannot be nested.
  .
• Interactive-Command Interface: Stops logging to the terminal and displays the Import prompt, from which you can enter various commands, some of which are specific to interactive-command mode. This mode is enabled by pressing Ctrl+C during an import operation started with the command-line interface or the parameter file interface. Interactive-command mode is also enabled when you attach to an executing or stopped job.

Data Pump Import Modes

One of the most significant characteristics of an import operation is its mode, because the mode largely determines what is imported. The specified mode applies to the source of the operation, either a dump file set or another database if the NETWORK_LINK parameter is specified.

When the source of the import operation is a dump file set, specifying a mode is optional. If no mode is specified, then Import attempts to load the entire dump file set in the mode in which the export operation was run.

The mode is specified on the command line, using the appropriate parameter. The available modes are as follows:

Full Import Mode

A full import is specified using the FULL parameter. In full import mode, the entire content of the source (dump file set or another database) is loaded into the target database. This is the default for file-based imports. You must have the IMP_FULL_DATABASE role if the source is another database or if the export operation that generated the dump file set required the EXP_FULL_DATABASE role.

Schema Mode

A schema import is specified using the SCHEMAS parameter. In a schema import, only objects owned by the current user are loaded. The source can be a full or schema-mode export dump file set or another database. If you have the IMP_FULL_DATABASE role, then a single schema other than your own schema, or a list of schemas can be specified and the schemas themselves (including system privilege grants) are created in the database in addition to the objects contained within those schemas.

Cross-schema references are not imported. For example, a trigger defined on a table within one of the specified schemas, but residing in a schema not explicitly specified, is not imported.

Table Mode

A table-mode import is specified using the TABLES parameter. In table mode, only the specified set of tables, partitions, and their dependent objects are loaded. The source can be a full, schema, tablespace, or table-mode export dump file set or another database. You must have the IMP_FULL_DATABASE role to specify tables that are not in your own schema.

Tablespace Mode

A tablespace-mode import is specified using the TABLESPACES parameter. In tablespace mode, all objects contained within the specified set of tablespaces are loaded. The source can be a full, schema, tablespace, or table-mode export dump file set or another database.

Transportable Tablespace Mode

A transportable tablespace import is specified using the TRANSPORT_TABLESPACES parameter. In transportable tablespace mode, the metadata from a transportable tablespace export dump file set or from another database is loaded. The datafiles specified by the TRANSPORT_DATAFILES parameter must be made available from the source system for use in the target database, typically by copying them over to the target system.

This mode requires the IMP_FULL_DATABASE role.

The figure below shows the directory name and the dump file name for the import file.

Figure. Parameters for the Import.