Oracle Exp/Imp Utility

Here are the main topics for this article:
  1.  What is the Import/ Export Utility ?
  2.  Which are the Import/ Export modes ?
  3.  Is it possible to exp/ imp to multiple files ?
  4.  How we can use exp/ imp when we have 2 different Oracle database versions?
  5. What I have to do before importing database objects ?
  6.  Is it possible to import a table in a different tablespace ?
  7.  In which cases imp/exp is used ?
  8.  How we can improve the EXP performance ?
  9.  How we can improve the IMP performance ?
10. Which are the EXP options ? 
11. Which are the IMP options ? 
12. Which are the common IMP/EXP problems ? 

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) TablespaceUse 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) TableSpecific 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 andenabled 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

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

9.  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.ora file
• (if possible) Set the LOG_BUFFER to a big value and restart oracle.

10.  Which are the EXP options ? 

EXP Option	Default value	Description
buffer		Specifies the size, in bytes, of the buffer (array) used to insert the data
compress	N	When “Y”, export will mark the table to be loaded as one extent for the import utility.  If “N”, the current storage options defined for the table will be used.  Although this option is only implemented on import, it can only be specified on export.
consistent	N	Specifies the set transaction read only statement for export, ensuring data consistency.  This option should be set to “Y” if activity is anticipated while the exp command is executing.  If ‘Y’ is set, confirm that there is sufficient undo segment space to avoid the export session getting the ORA-1555 Snapshot too old error.
constraints	Y	Specifies whether table constraints should be exported with table data.
direct	N	Determines whether to use direct or conventional path export.  Direct path exports bypass the SQL command, thereby enhancing performance.
feedback	0	Determines how often feedback is displayed.  A value of feedback=n displays a dot for every n rows processed.  The display shows all tables exported not individual ones.
file		The name of the export file. Multiple files can be listed, separated by commas.  When export fills thefilesize, it will begin writing to the next file in the list.
filesize		The maximum file size, specified in bytes.
flashback_scn		The system change number (SCN) that export uses to enable flashback.
flashback_time		Export will discover the SCN that is closest to the specified time.  This SCN is used to enable flashback.
full		The entire database is exported.
grants	Y	Specifies object grants to export.
help		Shows command line options for export.
indexes	Y	Determines whether index definitions are exported.  The index data is never exported.
log		The filename used by export to write messages.
object_consistent	N	Specifies whether export uses SET TRANSACTION READ ONLY to ensure that the data being exported is consistent.
owner		Only the owner’s objects will be exported.
parfile		The name of the file that contains the export parameter options.  This file can be used instead of specifying all the options on the command line for each export.
query		Allows a subset of rows from a table to be exported, based on a SQL where clause.
recordlength		Specifies the length of the file record in bytes.  This parameter affects the amount of data that accumulates before it is written to disk.  If not specified, this parameter defaults to the value specific to that platform.  The highest value is 64KB.
resumable	N	Enables and disables resumable space allocation.  When “Y”, the parameters resumable_name andresumable_timeout are utilized.
resumable_name		User defined string that helps identify a resumable statement that has been suspended.  This parameter is ignored unless resumable = Y.
resumable_timeout	2h	The time period in which an export error must be fixed.  This parameter is ignored unless resumable = Y.
rows	Y	Indicates whether or not the table rows should be exported.
statistics	ESTIMATE	Indicates the level of statistics generated when the data is imported.  Other options include COMPUTE and NONE.
tables		Indicates that the type of export is table-mode and lists the tables to be exported.  Table partitions and sub partitions can also be specified.
tablespaces		Indicates that the type of export is tablespace-mode, in which all tables assigned to the listed tablespaces will be exported.  This option requires the EXP_FULL_DATABASE role.
transport_tablespace	N	Enables the export of metadata needed for transportable tablespaces.
triggers	Y	Indicates whether triggers defined on export tables will also be exported.
tts_full_check	FALSE	When TRUE, export will verify that when creating a transportable tablespace, a consistent set of objects is exported.
userid		Specifies the userid/password of the user performing the export.
volsize		Specifies the maximum number of bytes in an export file on each tape volume.

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.

11.  Which are the IMP options ?

IMP Option	Default value	Description
buffer		Specifies the size, in bytes, of the buffer (array) used to insert the data
commit	N	Specifies whether import should commit after each array insert. By default, import commits after each table is loaded, however, this use a lot of the rollback segments or undo space for huge tables.
compile	Y	Tells import to compile procedural objects when they are imported.
constraints	Y	Specifies whether table constraints should also be imported with table data.
datafiles (only with transport_tablespace)		This parameter lists data files to be transported to the database.
destroy	N	Overwrite tablespace data file
feedback	0	Determines how often feedback is displayed. A value of feedback=100 displays a dot for every 100 rows processed. This option applies to the total tables imported, not individual ones. Another way to measure the number of rows that have been processed is to execute the following query while the import is active: SELECT rows_processed    FROM v$sqlarea    WHERE sql_text like 'INSERT %INTO "%'        AND command_type = 2        AND open_versions > 0;
file		The name of the export file to import. Multiple files can be listed, separated by commas. When export reaches the filesize it will begin writing to the next file in the list.
filesize		Maximum size of each dump file
fromuser		A comma delimited list of schemas from which to import. If the export file contains many users or even the entire database, the fromuser option enables only a subset of those objects (and data) to be imported.
full		The entire export file is imported.
grants	Y	Specifies to import object grants.
help		Shows command line options for importimp -help   or   imp help=y
ignore	N	Specifies how object creation errors should be handled. If a table already exists and ignore=y, then the rows are imported to the existing tables, otherwise errors will be reported and no rows are loaded into the table.
indexes	Y	Determines whether indexes are imported.
indexfile		Specifies a filename that contains index creation statements. This file can be used to build the indexes after the import has completed.
log		The filename used by import to write messages.
parfile		The name of the file that contains the import parameter options. This file can be used instead of specifying all the options on the command line.
recordlength		Specifies the length of the file record in bytes. This parameter is only used when transferring export files between operating systems that use different default values.
resumable	N	When “Y”, the parameters resumable_name and resumable_timeout are utilized.
resumable_name		User defined string that helps identify a resumable statement that has been suspended. This parameter is ignored unless resumable = Y.
resumable_timeout	2h	The time period in which an error must be fixed. This parameter is ignored unless resumable=Y.
rows	Y	Indicates whether or not the table rows should be imported.
show	N	When show=y, the DDL within the export file is displayed.
skip_unusable_indexes	N	Determines whether import skips the building of indexes that are in an unusable state.
statistics	ALWAYS	Determines the level of optimizer statistics that are generated on import. The options include ALWAYS, NONE, SAFE and RECALCULATE. ALWAYS imports statistics regardless of their validity. NONE does not import or recalculate any optimizer statistics. SAFE will import the statistics if they appear to be valid, otherwise they will be recomputed after import. RECALCULATE always generates new statistics after import.
streams_configuration	Y	Determines whether or not any streams metadata present in the export file will be imported.
streams_instantiation	N	Specifies whether or not to import streams instantiation metadata present in the export file
tables		Indicates that the type of export is table-mode and lists the tables to be exported. Table partitions and sub partitions can also be specified.
tablespaces		When transport_tablespace=y, this parameter provides a list of tablespaces.
to_user		Specifies a list of user schemas that will be targets for imports.
transport_tablespace	N	When Y, transportable tablespace metadata will be imported from the export file.
tts_owners		When transport_tablespace=Y, this parameter lists the users who own the data in the transportable tablespace set.
userid		Specifies the userid/password of the user performing the import.

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.

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.