Oracle Data Pump Import

The counterpart to the Data Pump Export utility is the Data Pump Import (impdp) utility. The impdp utility looks and feels much like the imp utility but has a number of additional features and is much more efficient than imp. This topic reviews Data Pump architectural specifics for the Data Pump Import program, examines those features of impd not found in imp and demonstrates the use of some of those features.

Features of Data Pump Import

Data Pump Import has a number of features not found in its predecessor, imp, including:

• Support for the use of metadata filters that allow you to control which object types (e.g., indexes, functions, or procedures) you import.
   
• The ability to suspend and resume an import job at will.
   
• The ability to attach or detach from a running import job at will.
   
• The ability to restart many failed jobs from the point of failure.
   
• The ability to control the number of threads that will be used during the import operation.
   
• Support for network mode operations, which allows you to load from another database directly.
   
• Support for direct mode import operations.
   
• Control over the version of the object that is imported. This allows you to import data from one version of Oracle and ensure that it is compatible with a lower-level version. Note that this applies to versions of Oracle after 10g and that this feature is not available for any version earlier than Oracle 10g.

There are some privileges that are required to use impd. If creation of the export dump files being used required exp_full_database rights, or if the import is done using the full parameter, then the user doing the import must have the imp_full_database privilege.

Using Data Pump Import

This section provides some details regarding use of the Data Pump Import program.

Data Pump Import: Parameters

A number of parameters are available when using Data Pump Import. The table below lists and describes each parameter. Examples of using most of these parameters are provided later in this topic.

Parameter	Description
attach	Indicates that Data Pump Import should attach to an existing job that is already running.
content	Allows you to control whether data or just database-related metadata is imported. Options: ALL, DATA_ONLY, and METADATA_ONLY.
data_options	(Oracle 11g) This parameter provides for special handling of certain types of data-related issues. See Oracle 11g Data Pump Enhancements for more information.
Directory	Defines the directory object to be used as the source of the dump files and log files. A directory of the same name must be created in the database that you are importing or an error will occur.
Dumpfile	Provides a list of the dump files to source the import from. Multiple dump files can be comma delimited. Also, a directory name can be included, separated from the filenames with a colon (:). Additionally, a substitution variable (%U) is available, which is a two-digit number from 01 to 99. This allows for the use of multiple dump files.
Estimate	Tells the impdp process how to calculate the amount of data that will be generated and provides information for percent complete operations. If a dump file is being used, no estimate is needed.
exclude	Excludes certain metadata from the import operation. Note that for any object excluded, any dependent objects also are excluded. For example, exclusion of a table also excludes any indexes, triggers, constraints and the related table data. You can also use wildcard characters and SQL statements to exclude specific ranges of objects.
flashback_scn	Allows you to use the flashback features when doing a network import directly from another source database. Used only in conjunction with the network_link parameter.
flashback_time	Allows you to use the flashback features when exporting the database. Used only in conjunction with the network_link parameter.
full	Indicates that impdp should import the entire database. The default for this parameter is N. If the network_link parameter is being used, or the creation of the source dump file set required the use of the exp_full_database privilege, then the user account used to load the dump file must have the imp_full_database privileges.
help	Allows you to display help messages and the syntax of impdp.
include	Allows you to define specific objects that you want imported. Only those objects, and dependent objects, are imported.
job_name	Creates an import job. This name is used to manage the job (for example, via the attach command). By default, this name is system generated using the naming convention sys_operation_mode_nn. For example, a full import might take the job name sys_import_full_01.
logfile	Identifies the log file that is generated during the import operation. By default, it's called import.log and stored in the location defined by the directory parameter.
network_link	Allows for a network import through a remote database link to a predefined source system.
nologfile	Suppresses the writing of the impdp log file. This is set to N by default.
parallel	Defines the maximum number of threads that can operate on behalf of the import job. The default for this setting is 1.
parfile	Allows you to define an external parameter file for the impdp process. The parameter file is local to the client and does not use a database directory as do the import dump files or log files.
partition_options	(Oracle 11g) This parameter allows some control over partitioning of tables during a Oracle Data Pump import. The following optioins are available: None - Tables will be imported such that they will look like those on the system on which the export was created.   Departition = Partitions will be created as individual tables rather than partitions of a partitioned table.   Merge - Causes all partitions to be merged into one, unpartitioned table. See Oracle 11g Data Pump Enhancements for more information.
query	Allows for the application of a SQL predicate to filter the data being imported.
remap_data	(Oracle 11g) Provides for the definition of one or more schema table.column object(s) to be remapped. See Oracle 11g Data Pump Enhancements for more information.
remap_datafile	Allows you to redefine the datafile names and directories during the import.
remap_schema	Allows you to map objects destined for one schema to another schema.
remap_table	(Oracle 11g) Use this parameter to rename a table during the import process. Define the original table schema and name, and then indicate the new name for the table. See Oracle 11g Data Pump Enhancements for more information.
remap_tablespace	Allows you to map objects to tablespaces other than the ones the objects was originally assigned to.
reuse_datafiles	Allows for re-creation of the tablespace datafiles.
reuse_dumpfiles	(Oracle 11g) This parameter allows you to overwrite any dump file that might already exist. See Oracle 11g Data Pump Enhancements for more information.
schemas	Defines the schemas you wish to import. Oracle creates the schemas, and then the schemas are imported. The user must have the exp_full_database privilege to use this command, or only the objects within the schema will be restored (no schema definition is imported).
skip_unusable_indexes	Indicates the indexes that have an UNUSABLE status should not be created.
sqlfile	Extracts all SQL DDL that is imported to an output file.
status	Defines how frequently the job status should be updated, in seconds. Defaults to 0 seconds.
streams_configuration	Indicates if any general Streams metadata in the export dump file should be imported.
table_exists_action	Determines the action to take if the table already exists. Options include: SKIP - Do not load the data (default) and move on to the next object.   APPEND - Append to existing data already in the table.   REPLACE - Drop the table, if it exists. Re-creates the table and loads the data.   TRUNCATE - Remove all rows before the load.
tables	Allows you to import specific tables only. Dependent objects are imported also.
tablespaces	Allows you to import objects listed in the specified tablespaces. All dependent objects in other tablespaces (e.g., indexes) also are imported.
transform	Allows you to alter the object creation DDL for either specific objects or all objects. This allows you to manipulate storage or physical attributes of objects.
transport_datafiles	Defines the list of datafiles in the source database that are to be imported into the target system by the transportable mode import. Used with the network_link parameter.
transport_full_check	During transportable tablespace operation, verifies that there are no dependencies between those objects inside the transportable set and those objects outside the transportable set. This is only valid when the network_link parameter is used.
transport_tablespaces	Allows for the import of transportable tablespace metadata.
transportable	(Oracle 11g) When the transportable parameter is used in impdp or expdp only the metadata associated with specific tables, partitions, or sub-partitions will be extracted, rather than all metadata. You can then proceed to transport the associated data files as you normally would. See Oracle 11g Data Pump Enhancements for more information.
version	Restricts objects being imported to a specific version level of the database. This is designed to help with compatibility issues when moving database objects from a higher version of the database to a lower version of the database.

Interactive mode has several commands available when called by impdp, as described below.

Command	Description
continue_client	Return to client logging mode
exit_client	Exit client logging mode without killing the job
help	Provides help on all commands
kill_job	Detach from and delete an existing job
parallel	Change the number of active workers for current job
start_job	Start or resume the current job
status	Define the refresh frequency for monitoring a job, in seconds (the default is 0 seconds)
stop_job	Stop job execution and exit the client

Note: The same pitfalls that apply to the Data Pump Export also apply to Oracle Data Pump Import.

Data Pump Import: Examples

This section includes several examples of the use of Data Pump Import. The examples do not include the output from most of these commands, only the text of the commands.

Example 1: A Full Database Import

This first example demonstrates using Data Pump Import to do a full database import:

C:\Oracle\Oracle Database 10g-beta2\BIN>impdp scott/tiger
dumpfile=pump_dir:mydb_%U.dat
nologfile=y job_name=robert full=y

Example 2: A Tablespace Import

This example imports objects contained in the USERS tablespace that is contained in the dump file mydb_tbs_users.dat:

C:\Oracle\Oracle Database 10g-beta2\BIN>impdp scott/tiger
dumpfile=pump_dir:mydb_tbs_users_%U.dat
nologfile=y job_name=tablespace tablespaces=users
table_exists_action=truncate

Example 3: Importing Only Specific Table Data

This is an example of importing data for a specific table from a dump file that was previously created. In this case, only data is imported into the SCOTT.EMP table only.

C:\Oracle\Oracle Database 10g-beta2\BIN>impdp scott/tiger
dumpfile=pump_dir:mydb_%U_data.dat
content=data_only job_name=data_import full=y
logfile=pump_log:mydb_imp.log tables=scott.emp

Example 4: Importing Specific Database Object Types

This example creates a parameter file that has individual INCLUDE statements. This export will export all database functions, procedures, and any table that has a name that starts with EMP.

-- Parameter file
INCLUDE=FUNCTION
INCLUDE=PROCEDURE
INCLUDE=TABLE:"LIKE 'EMP%'"
DUMPFILE=pump_dir:mydb_%U_objects.dat
NOLOGFILE=Y
JOB_NAME=specific_objects
FULL=y

Along with the preceding parameter file above, the following command line is used:

C:\Oracle\Oracle Database 10g-beta2\BIN>impdp scott/tiger
parfile=c:\oracle\admin\mydb\exp\mypar.fil