1. Utilities
  2. Oracle Data Pump
  3. Oracle Data Pump Import
  4. Starting Oracle Data Pump Import

3.2 Starting Oracle Data Pump Import

Start the Oracle Data Pump Import utility by using the impdp command.

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.

Note:

  • Do not start Import as SYSDBA, except at the request of Oracle technical support. SYSDBA is used internally and has specialized functions; its behavior is not the same as for general users.
  • Be aware that if you are performing a Data Pump Import into a table or tablespace created with the NOLOGGING clause enabled, then a redo log file may still be generated. The redo that is generated in such a case is generally for maintenance of the Data Pump control table, or related to underlying recursive space transactions, data dictionary changes, and index maintenance for indices on the table that require logging.
  • If the timezone version used by the export database is older than the version used by the import database, then loading columns with data type TIMESTAMP WITH TIMEZONE takes longer than it would otherwise. This additional time is required because the database must check to determine if the new timezone rules change the values being loaded.

Parent topic: Oracle Data Pump Import

3.2.1 Oracle Data Pump Import Interfaces

You can interact with Oracle 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. The use of parameter files is recommended if you are using parameters whose values require quotation marks.
  • 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.

Related Topics

Parent topic: Starting Oracle Data Pump Import

3.2.2 Oracle Data Pump Import Modes

The import mode that you use for Oracle Data Pump determines what is imported.

  • About Oracle Data Pump Import Modes
    Learn how Oracle Data Pump Import modes operate during the import.
  • Full Import Mode
    To specify a full import with Oracle Data Pump, use the FULL parameter.
  • Schema Mode
    To specify a schema import with Oracle Data Pump, use the SCHEMAS parameter.
  • Table Mode
    To specify a table mode import with Oracle Data Pump, use the TABLES parameter.
  • Tablespace Mode
    To specify a tablespace mode import with Oracle Data Pump, use the TABLESPACES parameter.
  • Transportable Tablespace Mode
    To specify a transportable tablespace mode import with Oracle Data Pump, use the TRANSPORT_TABLESPACES parameter.

Parent topic: Starting Oracle Data Pump Import

3.2.2.1 About Oracle Data Pump Import Modes

Learn how Oracle Data Pump Import modes operate during the import.

The Oracle Data Pump import mode that you specify for the import applies to the source of the operation. If you specify the NETWORK_LINK parameter, then that source is either a dump file set, or another database.

When the source of the import operation is a dump file set, specifying a mode is optional. If you do not specify a mode, 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.

Note:

When you import a dump file that was created by a full-mode export, the import operation attempts to copy the password for the SYS account from the source database. This copy sometimes fails (For example, if the password is in a shared password file). If it does fail, then after the import completes, you must set the password for the SYS account at the target database to a password of your choice.

Parent topic: Oracle Data Pump Import Modes

3.2.2.2 Full Import Mode

To specify a full import with Oracle Data Pump, use 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 mode is the default for file-based imports. If the source is another database containing schemas other than your own, then you must have the DATAPUMP_IMP_FULL_DATABASE role.

Cross-schema references are not imported for non-privileged users. For example, a trigger defined on a table within the schema of the importing user, but residing in another user schema, is not imported.

The DATAPUMP_IMP_FULL_DATABASE role is required on the target database. If the NETWORK_LINK parameter is used for a full import, then the DATAPUMP_EXP_FULL_DATABASE role is required on the source database

A full export does not export triggers owned by schema SYS. You must manually recreate SYS triggers either before or after the full import. Oracle recommends that you recreate them after the import in case they define actions that would impede progress of the import.

Using the Transportable Option During Full Mode Imports

You can use the transportable option during a full-mode import to perform a full transportable import.

Network-based full transportable imports require use of the FULL=YES, TRANSPORTABLE=ALWAYS, and TRANSPORT_DATAFILES=datafile_name parameters.

File-based full transportable imports only require use of the TRANSPORT_DATAFILES=datafile_name parameter. Data Pump Import infers the presence of the TRANSPORTABLE=ALWAYS and FULL=Y parameters.

There are several requirements when performing a full transportable import:

  • Either you must also specify the NETWORK_LINK parameter, or the dump file set being imported must have been created using the transportable option during export.
  • If you are using a network link, then the database specified on the NETWORK_LINK parameter must be Oracle Database 11g release 2 (11.2.0.3) or later, and the Oracle Data Pump VERSION parameter must be set to at least 12. (In a non-network import, VERSION=12 is implicitly determined from the dump file.)
  • If the source platform and the target platform are of different endianness, then you must convert the data being transported so that it is in the format of the target platform. To convert the data, you can use either the DBMS_FILE_TRANSFER package or the RMAN CONVERT command.
  • If the source and target platforms do not have the same endianness, then a full transportable import of encrypted tablespaces is not supported in network mode or in dump file mode

For a detailed example of performing a full transportable import, see Oracle Database Administrator’s Guide.

Related Topics

Parent topic: Oracle Data Pump Import Modes

3.2.2.3 Schema Mode

To specify a schema import with Oracle Data Pump, use the SCHEMAS parameter.

In a schema import, only objects owned by the specified schemas are loaded. The source can be a full, table, tablespace, or a schema-mode export dump file set, or another database. If you have the DATAPUMP_IMP_FULL_DATABASE role, then you can specify a list of schemas, 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 non-privileged users unless the other schema is remapped to the current schema. For example, a trigger defined on a table within the importing user's schema, but residing in another user's schema, is not imported.

Related Topics

Parent topic: Oracle Data Pump Import Modes

3.2.2.4 Table Mode

To specify a table mode import with Oracle Data Pump, use the TABLES parameter.

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 DATAPUMP_IMP_FULL_DATABASE role to specify tables that are not in your own schema.

You can use the transportable option during a table-mode import by specifying the TRANPORTABLE=ALWAYS parameter with the TABLES parameter. If you use this option, then you must also use the NETWORK_LINK parameter.

To recover tables and table partitions, you can also use RMAN backups, and the RMAN RECOVER TABLE command. During this process, RMAN creates (and optionally imports) an Oracle Data Pump export dump file that contains the recovered objects.

Related Topics

Parent topic: Oracle Data Pump Import Modes

3.2.2.5 Tablespace Mode

To specify a tablespace mode import with Oracle Data Pump, use the TABLESPACES parameter.

A tablespace-mode import is specified using the TABLESPACES parameter. In tablespace mode, all objects contained within the specified set of tablespaces are loaded, along with the dependent objects. The source can be a full, schema, tablespace, or table-mode export dump file set, or another database. For unprivileged users, objects not remapped to the current schema will not be processed.

Related Topics

Parent topic: Oracle Data Pump Import Modes

3.2.2.6 Transportable Tablespace Mode

To specify a transportable tablespace mode import with Oracle Data Pump, use the TRANSPORT_TABLESPACES parameter.

In transportable tablespace mode, the metadata from another database is loaded by using either a database link (specified with the NETWORK_LINK parameter), or by specifying a dump file that contains the metadata. The actual data files, 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.

When transportable jobs are performed, Oracle recommends that you keep a copy of the data files on the source system until the import job has successfully completed on the target system. With a copy of the data files, if the import job should fail for some reason, then you still have uncorrupted copies of the data files.

Using this mode requires the DATAPUMP_IMP_FULL_DATABASE role.

Note:

You cannot export transportable tablespaces and then import them into a database at a lower release level. The target database must be at the same or later release level as the source database.

Related Topics

Parent topic: Oracle Data Pump Import Modes

3.2.3 Network Considerations for Oracle Data Pump Import

Learn how Oracle Data Pump Import utility impdp identifies instances with connect identifiers in the connection string using Oracle*Net or a net service name, and how they are different from import operations using the NETWORK_LINK parameter.

When you start impdp, you can specify a connect identifier in the connect string that can be different from the current instance identified by the current Oracle System ID (SID).

You can specify a connect identifier by using either an Oracle*Net connect descriptor, or by using a net service name (usually defined in the tnsnames.ora file) that maps to a connect descriptor. Use of a connect identifier requires that you have Oracle Net Listener running (to start the default listener, enter lsnrctl start).

The following example shows this type of connection, in which inst1 is the connect identifier: 

impdp hr@inst1 DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp TABLES=employees

Import then prompts you for a password:

Password: password

To specify an Easy Connect string, the connect string must be an escaped quoted string. The Easy Connect string in its simplest form consists of a string database_host[:port][/[service_name]. For example, if the host is inst1, and you run Export on pdb1, then the Easy Connect string can be: 

impdp hr@\"inst1@example.com/pdb1" DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp TABLES=employees

If you prefer to use an unquoted string, then you can specify the Easy Connect connect string in a parameter file.

The local Import client connects to the database instance identified by the connect identifier inst1 (a net service name), and imports the data from the dump file hr.dmp to inst1.

Specifying a connect identifier when you start the Import utility is different from performing an import operation using the NETWORK_LINK parameter. When you start an import operation and specify a connect identifier, the local Import client connects to the database instance identified by the connect identifier and imports the data from the dump file named on the command line to that database instance.

By contrast, when you perform an import using the NETWORK_LINK parameter, the import is performed using a database link, and there is no dump file involved. (A database link is a connection between two physical database servers that allows a client to access them as one logical database.)

Related Topics

Parent topic: Starting Oracle Data Pump Import