|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:
catexp is called by catalog.sql.
Import export modes
exp/imp can be used in four modes:
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.
Specific tables (and partitions) can be exported/imported with table export mode.
Use the tables export parameter for a table export.
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 nth 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.
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:
A parfile (=parameter file) contains a list of export parameters.