Oracle Import/ Export Utility

1: What is the Import/ Export Utility ?

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.

2: Which are the Import/ Export modes ?

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. 

3: Is it possible to 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

4: How we can use exp/ imp when we have 2 different Oracle database versions?

exp must be of the lower version

imp must match the target version

5: What I have to do 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.
  

 

 

 

6: Is it possible to import a table in a different tablespace ?

By default, NO. Because 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

Here is an example of INDEXFILE:

7 : In which cases imp/exp is used ?

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

7: How we can improve the EXP 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. 

8: How we can improve the IMP 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.

9: Which are the common IMP/EXP problems?

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.