René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

exp and imp in Oracle

exp and imp are the executables that allow to make exports and imports of data objects (such as tables). Therefore, logical backups can be made with exp.
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.

Import export modes

exp/imp can be used in four modes:

Full export

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.

Tablespace

Use the tablespaces export parameter for a tablespace export.

User

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.

Table

Specific tables (and partitions) can be exported/imported with table export mode.
Use the tables export parameter for a table export.

exp

Objects owned by SYS cannot be exported.

Prerequisites

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.

Parameters

full

Use this parameter to specify full export mode.

tablespaces

Use this parameter to specify tablespace export mode.

owner

Use this parameter to specify user export mode.

tables

Use this parameter to specify table export mode.

direct

Used for a direct path export.

feedback=n

Prints a dot after each nth exported row.

flashback_scn

The exported data is consistent with the specified SCN.

flashback_time

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

consistent

object_consistent

query

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.

parfile

Specifies a parfile.

NLS_LANG settings

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

imp

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

Parameters

show

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

fromuser

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

Using imp/exp accross different Oracle versions

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.

Transportable tablespaces

The parfile

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