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 ?

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. 

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.
  

12. 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.

 

   More information about  this subject ( Oracle Import/ Export Utility ) you can get from  www.in-oracle.com