exp and imp in Oracle

exp and imp are the executables that allow to make exports and imports of data objects (such as tables).

exp/imp allow to transfer the data accross databases that reside on different hardware plattforms and/or on different Oracle versions. If the data is exported on a system with a different Oracle version then on that on which it is imported, imp must be the newer version. That means, if something needs to be exported from 10g into 9i, it must be exported with 9i's exp.

imp doesn't re-create an already existing table. It either errors out or ignores the errors.

In order to use exp and imp, the catexp.sql script must be run. catexp.sql basically creates the exp_full_database and imp_full_database roles. It is found under $ORACLE_HOME/rdbms/admin:

SQL> @?/rdbms/admin/catexp

catexp is called by catalog.sql.

exp/imp can be used in four modes:

The EXP_FULL_DATABASE and IMP_FULL_DATABASE, respectively, are needed to perform a full export.

Use the full export parameter for a full export.

Use the tablespaces export parameter for a tablespace export.

This mode can be used to export and import all objects that belong to a user.

Use the owner export parameter and the fromuser import parameter for a user (owner) export-import.

Specific tables (and partitions) can be exported/imported with table export mode.

Use the tables export parameter for a table export.

Objects owned by SYS cannot be exported.

One must have the create session privilege for being able to use exp. If objects of another user's schema need to be exported, the EXP_FULL_DATABASE role is required.

Use this parameter to specify full export mode.

Use this parameter to specify tablespace export mode.

Use this parameter to specify user export mode.

Use this parameter to specify table export mode.

Used for a direct path export.

Prints a dot after each n^th exported row.

The exported data is consistent with the specified SCN.

The exported data is consistent with a SCN that approximately matches that of the specified time.

Restricts the exported rows by means of a where clause.

The query parameter can only be used for table export mode. For obvious reasons, it must be appliable to all exported tables.

Specifies a parfile.

As exp and imp are client utilities they use the NLS_LANG settings.

See also nls_language.

If the parameter touser is used and (?) the export was made with FULL=YES, the users must already be created in the target database.

This parameter only shows the contents of an export file; it does not perform an import.

This parameter is used when an import in 'user export/import mode is made.

If exp and imp are used to export data from an Oracle database with a different version than the database in which is imported, then the following rules apply:

1. exp must be of the lower version
2. imp must match the target version.

A parfile (=parameter file) contains a list of export parameters.