Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Miscellaneous | Oracle RAC | Oracle Apps | Linux

Export And Import Enhancements In Oracle9i

A number of new features have been incorporated into the Export and Import utilities including:

FLASHBACK_SCN And FLASHBACK_TIME

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

The 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

The 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_NAME and RESUMABLE_TIMEOUT) to be used.

The 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 USER_RESUMABLE or DBA_RESUMABLE views.

The RESUMABLE_TIMEOUT parameter specifies the length of time the export can be suspended for before the underlying transactions aborts.

STATISTICS

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:
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

The 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 TABLESPACES parameter.

During Import the TABLESPACES parameter is used in conjunction with the TRANSPORT_TABLESPACE parameter 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

The TABLES parameter has been enhanced to allow pattern matching:
TABLES=(MYSCHEMA.ORDER%, MYSCHEMA.EMP%)
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.

Hope this helps. Regards Tim...

Back to the Top.