It's All About ORACLE

Oracle - The number one Database Management System. Hope this Blog will teach a lot about oracle.

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:

Oracle export / import


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 OptionDefault value  Description
bufferSpecifies the size, in bytes, of the buffer (array) used to insert the data
compressNWhen “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. 
consistentNSpecifies 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.
constraintsYSpecifies whether table constraints should be exported with table data.
directNDetermines whether to use direct or conventional path export.  Direct path exports bypass the SQL command, thereby enhancing performance.
feedback0Determines how often feedback is displayed.  A value of feedback=n displays a dot for every rows processed.  The display shows all tables exported not individual ones. 
fileThe 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.
filesizeThe maximum file size, specified in bytes. 
flashback_scnThe system change number (SCN) that export uses to enable flashback.
flashback_timeExport will discover the SCN that is closest to the specified time.  This SCN is used to enable flashback. 
fullThe entire database is exported.
grantsYSpecifies object grants to export.
helpShows command line options for export.
indexesYDetermines whether index definitions are exported.  The index data is never exported.
logThe filename used by export to write messages.
object_consistentNSpecifies whether export uses SET TRANSACTION READ ONLY to ensure that the data being exported is consistent. 
ownerOnly the owner’s objects will be exported.
parfileThe 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.
queryAllows a subset of rows from a table to be exported, based on a SQL where clause.
recordlengthSpecifies 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.  
resumableNEnables and disables resumable space allocation.  When “Y”, the parameters resumable_name andresumable_timeout are utilized. 
resumable_nameUser defined string that helps identify a resumable statement that has been suspended.  This parameter is ignored unless resumable = Y.
resumable_timeout2hThe time period in which an export error must be fixed.  This parameter is ignored unless resumable = Y.
rowsYIndicates whether or not the table rows should be exported.
statisticsESTIMATEIndicates the level of statistics generated when the data is imported.  Other options include COMPUTE and NONE.
tablesIndicates that the type of export is table-mode and lists the tables to be exported.  Table partitions and sub partitions can also be specified. 
tablespacesIndicates 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_tablespaceNEnables the export of metadata needed for transportable tablespaces.
triggersYIndicates whether triggers defined on export tables will also be exported.
tts_full_checkFALSEWhen TRUE, export will verify that when creating a transportable tablespace, a consistent set of objects is exported.
useridSpecifies the userid/password of the user performing the export.
volsizeSpecifies 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
bufferSpecifies the size, in bytes, of the buffer (array) used to insert the data
commitNSpecifies 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.
compileYTells import to compile procedural objects when they are imported.
constraintsYSpecifies 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.
destroyNOverwrite tablespace data file
feedback0Determines 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;
fileThe 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.
filesizeMaximum size of each dump file
fromuserA 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.
fullThe entire export file is imported.
grantsYSpecifies to import object grants.
helpShows command line options for importimp -help   or   imp help=y
ignoreNSpecifies 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.
indexesYDetermines whether indexes are imported.
indexfileSpecifies a filename that contains index creation statements. This file can be used to build the indexes after the import has completed.
logThe filename used by import to write messages.
parfileThe 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.
recordlengthSpecifies 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.
resumableNWhen “Y”, the parameters resumable_name and resumable_timeout are utilized.
resumable_nameUser defined string that helps identify a resumable statement that has been suspended. This parameter is ignored unless resumable = Y.
resumable_timeout2hThe time period in which an error must be fixed. This parameter is ignored unless resumable=Y.
rowsYIndicates whether or not the table rows should be imported.
showNWhen show=y, the DDL within the export file is displayed.
skip_unusable_indexesNDetermines whether import skips the building of indexes that are in an unusable state.
statisticsALWAYSDetermines 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_configurationYDetermines whether or not any streams metadata present in the export file will be imported.
streams_instantiationNSpecifies whether or not to import streams instantiation metadata present in the export file
tablesIndicates that the type of export is table-mode and lists the tables to be exported. Table partitions and sub partitions can also be specified.
tablespacesWhen transport_tablespace=y, this parameter provides a list of tablespaces.
to_userSpecifies a list of user schemas that will be targets for imports.
transport_tablespaceNWhen Y, transportable tablespace metadata will be imported from the export file.
tts_ownersWhen transport_tablespace=Y, this parameter lists the users who own the data in the transportable tablespace set.
useridSpecifies 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.

0 comments:

You Might Also Like

Related Posts with Thumbnails

Pages