Oracle export and import Utilities
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.