Oracle Import and Export: how to use it?

The Oracle export (exp) and import (imp) utilities are used to perform logical database backup and recovery, which allow you to write data into an operating system file and read it back into the Oracle database. They are also used to move Oracle schema and data from one machine to another, or one database to another.

The exp/imp utilities use an Oracle proprietary binary file format and can thus only be used between Oracle databases. One cannot export data and expect to import it into a non-Oracle database.

Different versions of the import utility are upwards compatible. This means that one can take an export file created from an old export version, and import it using a later version of the import utility. Oracle also ships some previous catexpX.sql (X=Oracle Version) scripts that can be executed as user SYS enabling older imp/exp versions to work (for backward compatibility). For example, one can run $ORACLE_HOME/rdbms/admin/catexp7.sql on an Oracle 8 database to allow the Oracle 7.3 exp/imp utilities to run against an Oracle 8 database.

EXPORT

Exporting an ORACLE database object is controlled by command-line parameters. To get familiar with EXPORT parameters, type the following in the SQL*Plus prompt:

exp help=y

Oracle EXPORT utility may be used in three ways:

• Interactive Mode: Simply type exp in command-line and answere the questions.
• With command-line parameters: exp <userid /password> <parameter>=<value>,<parameter>=<value>,...
• With parameters passed from a file: exp <userid /password> parfile=<filename>

IMPORT

Like EXPORT, the IMPORT utility is controlled by parameters. To get help with these parameters type:

imp help=y

Using IMPORT utility is similar to using EXPORT utility as described above.