8 Data Copying Using Export/Import

This chapter guides you through the process of using the Export and Import utilities to copy data from one Oracle Database to another in which the database release numbers are different. This chapter covers the following topics:

Export and Import Requirements
Upgrade the Database Using Export/Import

Export and Import Requirements

Dump files created by the Export utility can be imported into all future releases of the Oracle Database. For example, an Oracle8 export dump file can be imported by the oracle8i, Oracle9i, and Oracle Database 10g Import utilities.

Export dump files, however, are not downward compatible with the Import utilities of previous Oracle Database releases. That is, exported data cannot be imported by the Import utilities of previous Oracle Database releases. For example, an Oracle9i export dump file cannot be imported by an Oracle8i Import utility, and an Oracle Database 10g export dump file cannot be imported by an Oracle9i Import utility. The following tables provide specific examples.

Table 8-1 shows which releases to use when exporting data from release 10.2 and then importing that data into earlier releases.

Table 8-1 Exporting Data From Release 10.2 and Importing Into Earlier Releases

Export From	Import To	Use Export Utility For	Use Import Utility For
Release 10.2	Release 10.2	Release 10.2	Release 10.2
Release 10.2	Release 10.1	Release 10.1	Release 10.1
Release 10.2	Release 9.2	Release 9.2	Release 9.2
Release 10.2	Release 9.0.1	Release 9.0.1	Release 9.0.1
Release 10.2	Release 8.1.7	Release 8.1.7	Release 8.1.7
Release 10.2	Release 8.0.6	Release 8.0.6	Release 8.0.6

Table 8-2 shows which releases to use when exporting data from releases earlier than 10.2 and then importing that data into release 10.2.

Table 8-2 Exporting Data From Releases Earlier Than 10.2 and Importing Into Release 10.2

Export From	Import To	Use Export Utility For	Use Import Utility For
Release 10.1	Release 10.2	Release 10.1	Release 10.2
Release 9.2	Release 10.2	Release 9.2	Release 10.2
Release 8.1.7	Release 10.2	Release 8.1.7	Release 10.2
Release 8.0.6	Release 10.2	Release 8.0.6	Release 10.2
Release 7.3.4	Release 10.2	Release 7.3.4	Release 10.2

Export/Import Usage on Data Incompatible with a Previous Release

When you export data to a previous release, data that is incompatible with the previous release either is not exported at all or is exported with the loss of some features. For example, the new floating point datatypes in Oracle Database 10g will not be exported to an Oracle9i database.

In general, if you need to export data to a previous release, then first remove as many incompatibilities with the previous release as possible before you export the data.

Upgrade the Database Using Export/Import

To upgrade a database using the Export/Import utilities, complete the following steps:

Export data from the current database using the Export utility shipped with the current database. See the current database's utilities documents for information about using the Export utility on the current database.

To ensure a consistent export, make sure the current database is not available for updates during and after the export. If the current database will be available to users for updates after the export, then, prior to making the current database available, put procedures in place to copy the changes made in the current database to the new Oracle Database after the import is complete.
Install the new Oracle Database software. Installation is operating system-specific. Installation steps for Oracle Database are covered in your operating system-specific Oracle documentation.
If the new Oracle Database will have the same name as the current database, then shut down the current database before creating the new Oracle Database.
Create the new Oracle Database.

See Also:
Oracle Database Administrator's Guide for information about creating an Oracle Database
Start SQL*Plus in the new Oracle Database environment.
Connect to the database instance as a user with SYSDBA privileges.
Start an Oracle Database instance using STARTUP.
Pre-create tablespaces, users, and tables in the new database to improve space usage by changing storage parameters. When you pre-create tables using SQL*Plus, either run the database in the original database compatibility mode or make allowances for the specific data definition conversions that occur during import. You need to specify IGNORE=Y on Import when items have been pre-created.

Note:
If the new Oracle Database will be created on the same computer as the source database, and you do not want to overwrite the source database data files, then you must pre-create the tablespaces and specify DESTROY=N when you import.
Use the Import utility of the new Oracle Database to import the objects exported from the current database. Include the LOG parameter to save the informational and error messages from the import session to a file.

See Also:
Oracle Database Utilities for a complete description of the Import utility.
After the import, check the import log file for information about which imports of which objects completed successfully and, if there were failures, which failed.

See Also:
Oracle Database Utilities and the Oracle Database README.doc file for error handling information.
Use further Import scenarios (see Oracle Database Utilities) or SQL scripts that create the database's objects to clean up incomplete imports (or possibly to start an entirely new import).
If changes are made to the current database after the export, then make sure those changes are propagated to the new Oracle Database prior to making it available to users. See Step 1 for more information.
Complete the procedures described in Chapter 4, "After Upgrading a Database".