Import/Export in Oracle Database

Export (exp), Import (imp) are Oracle utilities which allow you to write data in an ORACLE-binary format from the database into operating system files and to read data back from those operating system files. It is the least option of backup and recoveries but it is useful in different scenarios.

 Modes of Import/Export

a) Full export/export

       The EXP_FULL_DATABASE & IMP_FULL_DATABASE, respectively, are needed to perform a full export. Use the full export parameter for a full export. 

b) Tablespace
Use the tablespaces export parameter for a tablespace export.

c) User
This mode can be used to export and import all objects that belong to a user. Use the owner export parameter and the fromuser import parameter for a user (owner) export-import. 

d) Table
Specific tables (or partitions) can be exported/imported with table export mode. Use the tables export parameter for a table export/ import mode. 

 Possibility of exp/ imp to multiple files

Yes, is possible. Here is an example:  

exp SCOTT/TIGER FILE=C:\backup\File1.dmp,C:\backup\File2.dmp LOG=C:\backup\scott.log

 Use of exp/ imp in different Oracle database versions

·         exp must be of the lower version

·         imp must match the target version

Steps before importing database objects

Before importing database objects, we have to drop or truncate the objects, if not, the data will be added to the objects. If the sequences are not dropped, the sequences will generate inconsistent values.  If there are any constraints on the target table, the constraints should be disabled during the import and enabled after import.
  

Possible to import a table in a different tablespace

By default we do not have possibility do to this. Because there is no tablespace parameter for the import operation.

However this could be done in the following manner:

·                    (re)create the table in another tablespace (the table will be empty)

·                    import the table using INDEXFILE parameter (the import is not  done, but a file which contains the indexes creation is generated)

·                    modify this script to create the indexes in the tablespace we want

·                    import the table using IGNORE=y option (because the table exists)

·                    recreate the indexes

 Frequent use of  imp/exp

Eliminate database fragmentation

·                    Schema refresh (move the schema from one database to another)

·                    Detect database corruption. Ensure that all the data can be read (if the data can be read that means there is no block corruption)

·                    Transporting tablespaces between databases

·                    Backup database objects

Enhancement of EXPORT performance

·                     Set the BUFFER parameter to a high value (e.g. 2M)

·                     If you run multiple export sessions, ensure they write to different physical disks. 

Enhancement of IMPORT performance

·         Import the table using INDEXFILE parameter (the import is not done, but a file which contains the indexes creation is generated), import the data and recreate the indexes

·         Store the dump file to be imported on a separate physical disk from the oracle data files

·         If there are any constraints on the target table, the constraints should be disabled during the import and enabled after import

·         Set the BUFFER parameter to a high value (ex. BUFFER=30000000 (~30MB)  ) and COMMIT =y  or set COMMIT=n (is the default behavior: import commits after each table is loaded, however, this use a lot of the rollback segments or undo space for huge tables.)

·         use the direct path to import the data (DIRECT=y)

·         (if possible) Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init<SID>.ora file

·         (if possible) Set the LOG_BUFFER to a big value and restart oracle.

  Available EXP options

·         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)

·         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) 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

·         VOLSIZE              number of bytes to write to each tape volume

·         TABLESPACES          list of tablespaces to export

·         TRANSPORT_TABLESPACE export transportable tablespace metadata (N)

·         TEMPLATE             template name which invokes iAS mode export 

Example: exp system/s              file=C:\emp.dmp tables=scott.emp log=C:\emp.log   (Windows)

      or      exp userid=system/s file=C:\emp.dmp tables=scott.emp log=C:\emp.log   (Windows)

USERID must be the first parameter on the command line.

  

Available IMP options

Keyword  Description (Default)       Keyword      Description (Default)

--------------------------------------------------------------------------

USERID   username/password           FULL         import entire file (N)

BUFFER   size of data buffer         FROMUSER     list of owner usernames

FILE     input files (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   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_INSTANTIATION  import streams instantiation metadata (N)

VOLSIZE                number of bytes in file on each volume of a file on tape

The following keywords only apply to transportable tablespaces

TRANSPORT_TABLESPACE import transportable tablespace metadata (N)

TABLESPACES tablespaces to be transported into database

DATAFILES datafiles to be transported into database

TTS_OWNERS users that own data in the transportable tablespace set

Example:  imp system/manager file=/APPS/x.dmp tables=x fromuser=cs touser=cs     (Unix)

   or          imp userid=system/manager file=/APPS/x.dmp tables=x fromuser=cs touser=cs   (Unix)

Note: USERID must be the first parameter on the command line.

 Common IMP/EXP errors

·                     ORA-00001: Unique constraint ... violated - Perhaps you are importing duplicate rows. Use IGNORE=N to skip tables that already exist (imp will give an error if the object is re-created) or the table could be dropped/ truncated and re-imported if we need to do a table refresh..

·                     IMP-00015: Statement failed ... object already exists... - Use the IGNORE=Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows.

·                     ORA-01555: Snapshot too old - Ask your users to STOP working while you are exporting or use parameter CONSISTENT=NO (However this option could create possible referential problems, because the tables are not exported from one snapshot in time).

·                     ORA-01562: Failed to extend rollback segment - Create bigger rollback segments or set parameter COMMIT=Y (with an appropriate BUFFER parameter ) while importing.