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)
| Keyword | Description (default value) |
|---|---|
| USERID | username/password |
| FULL | import entire file (N). To do the full database import, that user must have IMP_FULL_DATABASE role |
| BUFFER | size of data buffer. OS dependent |
| FROMUSER | list of owner usernames |
| FILE | input files (EXPDAT.DMP) |
| TOUSER | list of usernames |
| SHOW | just list file contents (N), will be used to check the validity of the dump file |
| TABLES | list of table names |
| IGNORE | ignore create errors (N) |
| RECORDLENGTH | length of IO record |
| GRANTS | import grants (Y) |
| INCTYPE | incremental import type. valid keywords are SYSTEM (for definitions), RESTORE (for data) |
| INDEXES | import indexes (Y) |
| COMMIT | commit array insert (N) |
| ROWS | import data rows (Y) |
| PARFILE | parameter filename |
| LOG | log file of screen output |
| CONSTRAINTS | import constraints (Y) |
| DESTROY | overwrite tablespace datafile (N) |
| INDEXFILE | will write DDLs of the objects in the dumpfile into the specified file |
| SKIP_UNUSABLE_INDEXES | skip maintenance of unusable indexes (N) |
| FEEDBACK | display progress every x rows(0) |
| TOID_NOVALIDATE | skip validation of specified type ids |
| FILESIZE | maximum size of each dump file |
| STATISTICS | import precomputed statistics (ALWAYS) |
| RESUMABLE | suspend when a space related error is encountered(N) |
| RESUMABLE_NAME | text string used to identify resumable statement |
| RESUMABLE_TIMEOUT | wait time for RESUMABLE |
| COMPILE | compile procedures, packages, and functions (Y) |
| STREAMS_CONFIGURATION | import streams general metadata (Y) |
| STREAMS_INSTANTIATION | import streams instantiation metadata (N) |
| VOLSIZE | number of bytes in file on each volume of a file on tape (depricated in Oracle 11g Release2) |
| DATA_ONLY | import 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)
| Keyword | Description (default value) |
|---|---|
| USERID | username/password |
| FULL | export entire file (N). To do full database export, that user must have EXP_FULL_DATABASE role |
| BUFFER | size of data buffer. OS dependent |
| OWNER | list of owner usernames |
| FILE | output files (EXPDAT.DMP) |
| TABLES | list of table names |
| COMPRESS | import into one extent (Y) |
| RECORDLENGTH | length of IO record |
| GRANTS | export grants (Y) |
| INCTYPE | incremental export type. valid values are COMPLETE, INCREMENTAL, CUMULATIVE |
| INDEXES | export indexes (Y) |
| RECORD | track incremental export (Y) |
| DIRECT | direct path (N) |
| TRIGGERS | export triggers (Y) |
| LOG | log file of screen output |
| STATISTICS | analyze objects (ESTIMATE) |
| ROWS | export data rows (Y) |
| PARFILE | parameter filename |
| CONSISTENT | cross-table consistency(N). Implements SET TRANSACTION READ ONLY |
| CONSTRAINTS | export constraints (Y) |
| OBJECT_CONSISTENT | transaction set to read only during object export (N) |
| FEEDBACK | display progress (a dot) for every N rows (0) |
| FILESIZE | maximum size of each dump file |
| FLASHBACK_SCN | SCN used to set session snapshot back to |
| FLASHBACK_TIME | time used to get the SCN closest to the specified time |
| QUERY | select clause used to export a subset of a table |
| RESUMABLE | suspend when a space related error is encountered(N) |
| RESUMABLE_NAME | text string used to identify resumable statement |
| RESUMABLE_TIMEOUT | wait time for RESUMABLE |
| TTS_FULL_CHECK | perform full or partial dependency check for TTS |
| VOLSIZE | number of bytes to write to each tape volume (not available from Oracle 11g Release2 |
| TABLESPACES | list of tablespaces to export |
| TRANSPORT_TABLESPACE | export transportable tablespace metadata (N) |
| TEMPLATE | template 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