Oracle’s export (exp/expdp) and import (imp/impdp) 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.
These utilities can be used to move data between different machines, databases or schema. However, as they use a proprietary binary file format, they can only be used between Oracle databases. One cannot export data and expect to import it into a non-Oracle database.
From Ver. 10g Oracle is recommending to use Data Pump Export and Import tools, which are enhanced versions of original Export and Import tools.
The export dump file contains objects in the following order:
- Type definitions
- Table definitions
- Table data
- Table indexes
- Integrity constraints, views, procedures, and triggers
- Bitmap, function-based, and domain indexes
When you import the tables the import tool will perform the actions in the following order, new tables are created, data is imported and indexes are built, triggers are imported, integrity constraints are enabled on the new tables, and any bitmap, function-based, and/or domain indexes are built. This sequence prevents data from being rejected due to the order in which tables are imported. This sequence also prevents redundant triggers from firing twice on the same data
When you import the tables the import tool will perform the actions in the following order, new tables are created, data is imported and indexes are built, triggers are imported, integrity constraints are enabled on the new tables, and any bitmap, function-based, and/or domain indexes are built. This sequence prevents data from being rejected due to the order in which tables are imported. This sequence also prevents redundant triggers from firing twice on the same data.
you can the run Oracle import/export Utilities with Command Line Mode and Interactive Mode.
Run Oracle export Utilities with Command Line parameters:
KeywordDescription (Default)
| USERID | username/password |
| BUFFER | size of data buffer |
| FILE | output files (EXPDAT.DMP) |
| COMPRESS | import into one extent (Y) |
| GRANTS | export grants (Y) |
| INDEXES | export indexes (Y) |
| DIRECT | direct path (N) |
| LOG | log file of screen output |
| ROWS | export data rows (Y) |
| CONSISTENT | cross-table consistency(N) |
| FULL | export entire file (N) |
| OWNER | list of owner usernames |
| TABLES | list of table names |
| RECORDLENGTH | length of IO record |
| INCTYPE | incremental export type |
| RECORD | track incr. export (Y) |
| TRIGGERS | export triggers (Y) |
| STATISTICS | analyze objects (ESTIMATE) |
| PARFILE | parameter filename |
| CONSTRAINTS | export constraints (Y) |
| OBJECT_CONSISTENT | transaction set to read only during object export (N) |
| FEEDBACK | display progress every x 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 |
| TABLESPACES | list of tablespaces to export |
| TRANSPORT_TABLESPACE | export transportable tablespace metadata (N) |
| TEMPLATE | template name which invokes iAS mode export |
Example of Exporting Full Database
The following example shows how to export full database
$exp USERID=scott/tiger FULL=y FILE=oracledump.dmp
Example of Exporting Schema
To export Objects stored in a particular schemas you can run export utility with the following arguments
$exp USERID=scott/tiger OWNER=(SCOTT,ALI) FILE=exp_own.dmp
Using Oracle Import Utility
Objects exported by export utility can only be imported by Import utility. Import utility can run in Interactive mode or command line mode.
Run Oracle Import Utilities with Command Line parameters:
KeywordDescription (Default)
| USERID | username/password |
| BUFFER | size of data buffer |
| FILE | input files (EXPDAT.DMP) |
| SHOW | just list file contents (N) |
| IGNORE | ignore create errors (N) |
| GRANTS | import grants (Y) |
| INDEXES | import indexes (Y) |
| ROWS | import data rows (Y) |
| LOG | log file of screen output |
| FULL | import entire file (N) |
| FROMUSER | list of owner usernames |
| TOUSER | list of usernames |
| TABLES | list of table names |
| RECORDLENGTH | length of IO record |
| INCTYPE | incremental import type |
| COMMIT | commit array insert (N) |
| PARFILE | parameter filename |
| CONSTRAINTS | import constraints (Y) |
| DESTROY | overwrite tablespace data file (N) |
| INDEXFILE | write table/index info to 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_INSTANITATION | import streams instantiation metadata (N) |
Example Importing Tables from one account to another account:
$imp cc_03/tartan@in01 fromuser=demo touser=cc_03 file=oracle_demo9311.dmp log=log1.log
I supposed user demo has exported tables into a dump file oracle_demo9311.dmp. and now we will import all tables,indexs,views, triggers and etc to another user cc_03, the instance name is in01which has configed to connect oracle db server. the dump file name is oracle_demo9311.dmp and located in the same directory that you will run the commend line under it. all logs or warning messages are processed in import unities will be stored in the file log1.log.
Example Importing Tables Using Pattern Matching
Suppose you want to import all tables from a dump file whose name matches a particular pattern. To do so, use “%” wild character in TABLES option. For example, the following command will import all tables whose names starts with alphabet “e” and those tables whose name contains alphabet “d”
$imp scott/tiger FILE=myfullexp.dmp FROMUSER=scott TABLES=(a%,%d%)