Export And Import Enhancements In Oracle9i
A number of new features have been incorporated into the Export and Import utilities including the following.
- FLASHBACK_SCN And FLASHBACK_TIME
- TABLES Pattern Matching
- Reduced Character Set Conversions
FLASHBACK_SCN And FLASHBACK_TIME
FLASHBACK_SCN parameter specifies the system change number (SCN) that Export will use to enable flashback. The export file contains data that is consistent with the specified SCN.
FLASHBACK_TIME parameter specifies the system time that Export will use to enable flashback. This time is used to find the SCN that most closely matches. The resultant SCN is used during the Export.
RESUMABLE parameter is used to enable and disable resumable space allocation. The default value for the parameter is 'N' so it must be explicitly set to 'Y' for its associated parameters (
RESUMABLE_TIMEOUT) to be
RESUMABLE_NAME parameter allows a text name to be assigned to the transaction. This name can subsequently be used
to identify suspended transactions when queried using the
RESUMABLE_TIMEOUT parameter specifies the length of time the export can be suspended for before the underlying
For more information on resumable space allocation, read this article.
During Export this parameter specifies the type of database optimizer statistics to generate when the exported data is imported. The options are ESTIMATE (default), COMPUTE, and NONE. The export file may contain some precalculated statistics along with the ANALYZE statements. These statistics will not be used at export time if a table has columns with system-generated names.
The precalculated optimizer statistics are flagged as questionable at export time if:
- Row errors occur during the export.
- The client and server character sets do not match.
- A QUERY clause is used.
- All partitions or subpartitions are not exported.
During Import the parameter options are ALWAYS (default), NONE, SAFE and RECALCULATE. The ALWAYS option imports all statistics, even those that are questionable. The NONE option does not import or recalculate statistics. The SAFE option imports statistics that are not questionable, but recalculates those that are. Finally, the RECACULATE option recaculates all statistics rather than importing them.
TABLESPACES parameter is used to export all tables contained within the specified tablespace or tablespace list. The table indexes are included regardless of their tablespace location. Partitioned tables are included if one or more partitions are located in the specified tablespace. The
EXP_FULL_DATABASE role is required to use
During Import the
TABLESPACES parameter is used in conjunction with the
to specify the transportable tablespaces that should be imported. This functionality is unchanged and has no relationship to the
TABLESPACES parameter used for export.
TABLES Pattern Matching
TABLES parameter has been enhanced to allow pattern matching.
In the above example, any tables located in MYSCHEMA whose name begins with ORDER or EMP will be exported.
Reduced Character Set Conversions
All user data in text related datatypes is exported using the character set of the source database. If the character sets of the source and target databases do not match a single conversion is performed.
The export and import process can require up to three conversions for DDL. The DDL is exported using the character set specified by the NLS_LANG environment variable. A character set conversion is performed if this differs from the source database. If the export character set differs from that specified by the import users NLS_LANG parameter another conversion is performed. This conversion cannot be done for multibyte character sets so these must match. A final characterset conversion is needed if the import session and the target database have different character sets. To eliminate possible data loss caused by conversions, make sure all environments have matching character sets.
For more information see:
Hope this helps. Regards Tim...