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 . It is found under $ORACLE_HOME/rdbms/admin:
SQL> @?/rdbms/admin/catexp
catexp is called by .
Import export modes
exp/imp can be used in four modes:
Full export
The and , respectively, are needed to perform a full export.
Use the export parameter for a full export.
Tablespace
Use the 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 export parameter and the import parameter for a user (owner) export-import.
Table
Specific tables (and partitions) can be exported/imported with table export mode.
Use the export parameter for a table export.
exp
Objects owned by cannot be exported.
Prerequisites
One must have the create session for being able to use exp. If objects of another user's schema need to be exported, the role is required.
Parameters
full
Use this parameter to specify .
tablespaces
Use this parameter to specify .
owner
Use this parameter to specify .
tables
Use this parameter to specify .
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 .
flashback_time
The exported data is consistent with a 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 . For obvious reasons, it must be appliable to all exported tables.
parfile
Specifies a .
NLS_LANG settings
As exp and imp are client utilities they use the NLS_LANG settings.
See also .
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 ' 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:
- exp must be of the lower version
- imp must match the target version.
Transportable tablespaces
The parfile
A parfile (=parameter file) contains a list of .
