Oracle export and import Utilities

Updated   Read time 5 min(s) 11 sec(s)   (2395 views).  Database  

Export (exp) and Import (imp) are the Oracle utilities that allow us to make exports and imports of the data objects, and transfer the data across databases that reside on different hardware platforms on different Oracle versions.

These utilities are used to perform logical database backup and recovery. When exporting, database objects are dumped to a binary file which can then be imported into another Oracle database.

Invoking Export and Import

You can run Export and Import tool in two modes:

  • Command Line Mode
  • Interactive Mode

When you just type exp or imp at o/s prompt it will run in interactive mode i.e. these tools will prompt you for all the necessary input. If you supply command line arguments when calling exp or imp then it will run in command line mode

Note: Before using these commands, you should set ORACLE_HOME, ORACLE_SID and PATH environment variables.

Command Line Syntax

exp

exp USERID KEYWORD=value or KEYWORD=(value1,value2,...,valueN)

for example:

$ exp USERID=scott/tiger FULL=y FILE=full.dmp

In the above command, FILE option specifies the name of the dump file, FULL option specifies that you want to export the full database, USERID option specifies the user account to connect to the database. Note, to perform full export the user should have DBA or EXP_FULL_DATABASE privilege.

imp

imp USERID KEYWORD=value or KEYWORD=(value1,value2,...,valueN)

where USERID=username/password

for example:

$ imp scott/tiger FULL=y FILE=full.dmp

In the above example, scott/tiger is the user account to connect to the database, FILE option specifies the name of the dump file, FULL option specifies that you want to import the enitre export file.

Getting help

Both export and import utilities have command line help that can assist:

exp help=y
imp help=y
Notes
The USERID must be the first parameter on the command line
Any objects owned by SYS cannot be exported
If you want to export objects of another schema, you need EXP_FULL_DATABASE role.
imp provides backward compatibility (i.e: it will allows you to import the objects that you have exported in earlier releases of on an Oracle RDBMS)
imp doesn't recreate already existing objects. It either abort the import process (default) or ignores the errors (if you specify IGNORE=Y).

Examples

To export Objects stored in a particular schemas you can run export utility with the following arguments

$ exp USERID=scott/tiger OWNER=(SCOTT,JONES) FILE=exp_own.dmp

The above command will export all the objects stored in SCOTT and JONES's schema.

To export individual tables give the following command

$ exp USERID=scott/tiger TABLES=(scott.emp,scott.sales) FILE=exp_tab.dmp

This will export scott's emp and sales tables.

Full database export using parameter file:

$ exp PARFILE=param.dat

where parameter file (param.dat):

USERID=SYSTEM/pass@s3
FULL=Y
COMPRESS=Y
CONSISTENT=Y
LOG=export.log

Export all objects owned by user pro17 (the whole user's schema):

$ exp system owner=pro17

Export table usrlist owned by appadm:

$ exp SYSTEM TABLES=APPADM.USRLIST
GRANTS=Y INDEXES=N LOG=EXPORT.LOG

Being Oracle user dbsman export (over a network) some tables:

$ exp dbsman@s4 tables=(usrlist,jobs,depts)

Full database export, old style, Windows / Oracle 7.3:

$ exp73 SYSTEM@s2 FULL=Y LOG=EXPORT.LOG

Full database import (requires some preliminary work); full db export file (expdat.dmp) must be present in the current working directory:

$ imp SYSTEM FULL=Y LOG=IMPORT.LOG

Being Oracle user dbsman import some of your tables from the default export file (expdat.dmp, must be in the current working dir):

$ imp dbsman tables=(usrlist,jobs,depts)

Import using parameter file:

$ imp PARFILE=param.dat

where parameter file to be used to import a table to another schema assuming you have a full database export file expdat.dmp (you'll be prompted for SYSTEM's password):

USERID=system
TABLES=(hw_computers)
FROMUSER=pro08
TOUSER=dbsman
LOG=imp002.log

Parameter file to be used to import some user tables from a full database export file (database is available through srv2 alias; user must exist; you'll be prompted for SYSTEM's password):

USERID=system@srv2
TABLES=(usrlist,usrlist2,hw_ip_addr)
FROMUSER=eco30
TOUSER=eco30
LOG=imp005.log

Param file to be used to import the whole user's schema from a full db exp file (user must exist; you'll be prompted for SYSTEM's password):

USERID=system
FROMUSER=a301
TOUSER=a301
LOG=imp007.log

If tables being imported already exist in the database and there are referential constraints between them, some/all rows may be rejected due to constraint violation. Tables are imported in the order of their location in exp dump file, which can be wrong for constraints. In this case you should import related tables separately, in the right order. Or, you can disable constraints and re-enable after import. Also, if tables exist, use IGNORE=y, otherwise when imp fails to create table, it does not attempt to insert rows - just skips to the next object.

USERID=SYSTEM
FILE=expdat.dmp
GRANTS=n
IGNORE=y
BUFFER=262144
FROMUSER=dbsman
TOUSER=dbsman
TABLES=(tab0202,tab0220)
LOG=imp0029.log

Improving exports and imports

The following are examples of how to improve the export and import process

exp:

  • Set the BUFFER parameter to a high value. Default is 256KB.
  • Stop unnecessary applications to free the resources.
  • If you are running multiple sessions, make sure they write to different disks.
  • Do not export to NFS (Network File Share). Exporting to disk is faster.
  • Set the RECORDLENGTH parameter to a high value.
  • Use DIRECT=yes (direct mode export).

imp:

  • Place the file to be imported in separate disk from datafiles.
  • Increase the DB_CACHE_SIZE.
  • Set LOG_BUFFER to big size.
  • Stop redolog archiving, if possible.
  • Use COMMIT=n, if possible.
  • Set the BUFFER parameter to a high value. Default is 256KB.
  • It's advisable to drop indexes before importing to speed up the import process or set INDEXES=N and building indexes later on after the import. Indexes can easily be recreated after the data was successfully imported.
  • Use STATISTICS=NONE
  • Disable the INSERT triggers, as they fire during import.
  • Set Parameter COMMIT_WRITE=NOWAIT(in 10g) or COMMIT_WAIT=NOWAIT (in 11g) during import.