Import And Export Utilities in Oracle Database

Import and Export

The import and export utilities are used to move data from one instance to another, or one schema to another. The utility basically re-reruns all the DDL, in order, to recreate and fill the tables.

/* Export sample call */

exp username/password feedback=100 file=filename.dat log=export.log

exp help=y

Keyword  Description (Default)        Keyword      Description (Default)
--------------------------------------------------------------------------
USERID   username/password            FULL         export entire file (N)
BUFFER   size of data buffer          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
INDEXES  export indexes (Y)           RECORD       track incr. export (Y)
ROWS     export data rows (Y)         PARFILE      parameter filename
CONSTRAINTS export constraints (Y)    CONSISTENT   cross-table consistency
LOG      log file of screen output    STATISTICS   analyze objects (ESTIMATE)
DIRECT   direct path (N)              TRIGGERS     export triggers (Y)
FEEDBACK display progress every x rows (0)
FILESIZE maximum size of each dump file
QUERY    select clause used to export a subset of a table
VOLSIZE  number of bytes to write to each tape volume

The following keywords only apply to transportable tablespaces
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TABLESPACES list of tablespaces to transport



/* Import sample calls */

imp username/password feedback=100 file=filename.dat log=import.log

/* import a single table, table exists already */
imp admin23/portal feedback=100 file=company3.dat log=company.log tables='(company)' ignore=Y

imp help=y

USERID   username/password           FULL     import entire file (N)
BUFFER   size of data buffer         FROMUSER     list of owner usernames
FILE     output file (EXPDAT.DMP)    TOUSER       list of usernames
SHOW     just list file contents (N) TABLES       list of table names
IGNORE   ignore create errors (N)    RECORDLENGTH length of IO record
GRANTS   import grants (Y)           INCTYPE      incremental import type
INDEXES  import indexes (Y)          COMMIT    commit array insert (N)
ROWS     import data rows (Y)        PARFILE      parameter filename
LOG      log file of screen output

DESTROY                  overwrite tablespace data file (N)
INDEXFILE                write table/index info to specified file
CHARSET                  character set of export file (NLS_LANG)
POINT_IN_TIME_RECOVER    Tablespace Point-in-time Recovery (N)
SKIP_UNUSABLE_INDEXES    skip maintenance of unusable indexes (N)
ANALYZE                  execute ANALYZE statements in dump file (Y)
FEEDBACK                 display progress every x rows(0)