Oracle Import and Export utilities

Both the imp and exp are the Oracle utilities that allow us to make imports and exports of Oracle data objects, and transfer the data across databases that reside on different hardware platforms, or different Oracle releases.

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

The file $ORACLE_HOME/rdbms/admin/catexp.sql will create IMP_FULL_DATABASE and EXP_FULL_DATABASE roles. However, there is no need to run this if catalog.sql was run at database creation.

Oracle best practices recommend that you set ORACLE_HOME, ORACLE_SID, and PATH environment variables before performing an import or export.

Getting help?

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

$ imp help=y
$ exp help=y

Oracle Import (imp) Utility

imp provides backward compatibility i.e. it will allows you to import the objects that you have exported in lower Oracle versions also.

The imp doesn't recreate already existing objects. It either aborts the import process (default) or ignores the errors (if you specify IGNORE=Y).

SYNOPSIS:

imp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
KeywordDescription (default value)
USERIDusername/password
FULLimport entire file (N). To do the full database import, that user must have IMP_FULL_DATABASE role
BUFFERsize of data buffer. OS dependent
FROMUSERlist of owner usernames
FILEinput files (EXPDAT.DMP)
TOUSERlist of usernames
SHOWjust list file contents (N), will be used to check the validity of the dump file
TABLESlist of table names
IGNOREignore create errors (N)
RECORDLENGTHlength of IO record
GRANTSimport grants (Y)
INCTYPEincremental import type. valid keywords are
SYSTEM (for definitions), RESTORE (for data)
INDEXESimport indexes (Y)
COMMITcommit array insert (N)
ROWSimport data rows (Y)
PARFILEparameter filename
LOGlog file of screen output
CONSTRAINTSimport constraints (Y)
DESTROYoverwrite tablespace datafile (N)
INDEXFILEwill write DDLs of the objects in the dumpfile into the specified file
SKIP_UNUSABLE_INDEXESskip maintenance of unusable indexes (N)
FEEDBACKdisplay progress every x rows(0)
TOID_NOVALIDATEskip validation of specified type ids
FILESIZEmaximum size of each dump file
STATISTICSimport precomputed statistics (ALWAYS)
RESUMABLEsuspend when a space related error is encountered(N)
RESUMABLE_NAMEtext string used to identify resumable statement
RESUMABLE_TIMEOUTwait time for RESUMABLE
COMPILEcompile procedures, packages, and functions (Y)
STREAMS_CONFIGURATIONimport streams general metadata (Y)
STREAMS_INSTANTIATIONimport streams instantiation metadata (N)
VOLSIZEnumber of bytes in file on each volume of a file on tape (depricated in Oracle 11g Release2)
DATA_ONLYimport only data (N) (new in Oracle 11g Release2)

Examples

  • importing all the exported data.
    $ imp system/manager file=emp.dmp log=emp_imp.log full=y
  • checks the validity of the dumpfile.
    $ imp file=myexp.dmp log=myexp.log show=y
  • importing all the records of table (employee table records in workers schema).
    $ imp system/manager file=emp.dmp log=emp_imp.log tables=workers.employee
  • importing all the records for two tables.
    $ imp FILE=library.dmp LOG=library.log IGNORE=Y GRANTS=N INDEXES=N COMMIT=Y TABLES=(genre, title)
  • importing data of one schema into another schema
    $ imp system/manager file=puppets.dmp log=puppets.log FROMUSER=pinky TOUSER=perky ignore=y
  • importing definitions from backup.
    $ imp file=full_exp.dmp log=full_exp.log inctype=system
  • importing data from backup.
    $ imp file=full_exp.dmp log=full_exp.log inctype=restore
  • write DDLs of the objects in exported dumpfile (scott schema) into specified file. This command does not import the objects.
    $ imp file=scott.dmp log=scott.log indexfile=scott_schema.sql

Oracle Export (exp) Utility

When you run exp against an Oracle database, objects (such as tables) are extracted, followed by their related objects (such as indexes, comments, and grants), if any. The extracted data is written to an export dump file.

NOTE: If you want to export objects of another schema, you need EXP_FULL_DATABASE role.

NOTE: Objects owned by SYS cannot be exported.

SYNOPSIS:

exp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
KeywordDescription (default value)
USERIDusername/password
FULLexport entire file (N). To do full database export, that user must have EXP_FULL_DATABASE role
BUFFERsize of data buffer. OS dependent
OWNERlist of owner usernames
FILEoutput files (EXPDAT.DMP)
TABLESlist of table names
COMPRESSimport into one extent (Y)
RECORDLENGTH length of IO record
GRANTSexport grants (Y)
INCTYPEincremental export type. valid values are
COMPLETE, INCREMENTAL, CUMULATIVE
INDEXESexport indexes (Y)
RECORDtrack incremental export (Y)
DIRECTdirect path (N)
TRIGGERSexport triggers (Y)
LOGlog file of screen output
STATISTICSanalyze objects (ESTIMATE)
ROWSexport data rows (Y)
PARFILEparameter filename
CONSISTENTcross-table consistency(N). Implements SET TRANSACTION READ ONLY
CONSTRAINTSexport constraints (Y)
OBJECT_CONSISTENTtransaction set to read only during object export (N)
FEEDBACKdisplay progress (a dot) for every N rows (0)
FILESIZEmaximum size of each dump file
FLASHBACK_SCNSCN used to set session snapshot back to
FLASHBACK_TIMEtime used to get the SCN closest to the specified time
QUERYselect clause used to export a subset of a table
RESUMABLEsuspend when a space related error is encountered(N)
RESUMABLE_NAMEtext string used to identify resumable statement
RESUMABLE_TIMEOUTwait time for RESUMABLE
TTS_FULL_CHECKperform full or partial dependency check for TTS
VOLSIZEnumber of bytes to write to each tape volume (not available from Oracle 11g Release2
TABLESPACESlist of tablespaces to export
TRANSPORT_TABLESPACEexport transportable tablespace metadata (N)
TEMPLATEtemplate name which invokes iAS mode export

Examples

  • exporting full database.
    $ exp system/manager file=emp.dmp log=emp_exp.log full=y
  • exporting all the objects of a schema.
    $ exp system/manager file=owner.dmp log=owner.log owner=owner direct=y STATISTICS=none
  • exporting all the objects of multiple schemas.
    $ exp file=schemas.dmp log=schemas.log owner=master,owner,user direct=y STATISTICS=none
  • exporting all the rows in table (emp table records in scott schema).
    $ exp file=emp.dmp log=emp.log tables=scott.emp direct=y STATISTICS=none
  • exporting the records of some tables which satisfies a particular criteria.
    $ exp file=products.dmp log=products.log tables=products.UPC,products.SHORTNAME
    query=\"where CODE in \(\'ABCD1234\',\'ZYXW9876\'\)\" statistics=none
  • exporting at tablespace level.
    $ exp transport_tablespace=y tablespaces=library statistics=none file=library.dmp log=library_exp.log
  • exporting to multiple files.
    $ exp FILE=file1.dmp,file2.dmp,file3.dmp FILESIZE=10M LOG=multiple.log
  • exporting full database (after some incremental/cumulative backups).
    $ exp file=scott.dmp log=scott.log inctype=complete
  • exporting cumulatively (taking backup from last complete or cumulative backup).
    $ exp file=scott.dmp log=scott.log inctype=cumulative
  • exporting incrementally (taking backup from last complete or cumulative or incremental backup).
    $ exp file=scott.dmp log=scott.log inctype=incremental