Compatibility Matrix for Export And Import Between Different Oracle Versions

In this Document
  Purpose
  Scope and Application
  Compatibility Matrix for Export And Import Between Different Oracle Versions [Video]
     1. Summary.
     2. Introduction.
     3. Data Dictionary Views.
     4. Database Migration with a full database export/import.
     5. Basic Compatibility.
     6. Limitations.
     7. Examples.
     8. Possible error messages.
     9. Remarks.
  References

---

Applies to:

Oracle Server - Personal Edition - Version: 7.3.2.0 to 11.2.0.1 - Release: 7.3.2 to 11.2
Oracle Server - Standard Edition - Version: 7.3.2.0 to 11.2.0.1   [Release: 7.3.2 to 11.2]
Enterprise Manager for RDBMS - Version: 8.1.7.4 to 11.2.0.1   [Release: to 11.2]
Oracle Server - Enterprise Edition - Version: 7.3.2.0 to 11.2.0.1   [Release: 7.3.2 to 11.2]
Oracle Server - Personal Edition - Version: 7.3.2.0 to 11.1.0.7   [Release: 7.3.2 to 11.1]
Information in this document applies to any platform.

Purpose

This article describes several issues related with the compatibility of the different versions of the Oracle Export and Import utilities.

Scope and Application

The article is intended for users of the Oracle7, Oracle8, Oracle8i, Oracle9i, Oracle10g, and Oracle11g databases who wish to use the EXPORT and IMPORT utilities to export data from an Oracle database release x and import this data into an Oracle database release y. The article gives information which version of the utility to use when performing the export and import, and how to prepare the Oracle data dictionary before exporting the data.
For compatibility rules for the enhanced utilities Export Data Pump (expdp) and Import Data Pump (impdp) which were introduced in Oracle10g, see:
Note:553337.1 "Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions"
For the transportable tablespaces export/import, see:
Note:291024.1 "Compatibility and New Features when Transporting Tablespaces with Export and Import"
In the text below, the following terminology is used:

• Source database = the database where the data is exported from.
• Target database = the database where the data is imported into.

Compatibility Matrix for Export And Import Between Different Oracle Versions [Video]

1. Summary.

2. Introduction.

2.1. Export and Import clients.
With the Oracle EXPORT and IMPORT utilities you can transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations.
The Oracle EXPORT utility extracts the object definitions and table data from an Oracle database and stores them in an Oracle binary-format export dump file located typically on disk or tape. The files can then be transferred using FTP or physically transported (in the case of tape) to a different site. When transferring the export dump file over a network, the file has to be transmitted in binary mode. Transmitting export files in character mode (ASCII) causes errors when the file is imported, resulting most likely in: IMP-9 or IMP-10, followed by IMP-0.
The Oracle IMPORT utility reads the object definitions and table data from the export dump file and then inserts the information into the Oracle database.

2.2. Export Data Pump and Import Data Pump clients.
Starting with Oracle10g Release 1 (10.1.0.x) we provide the new Export DataPump (expdp) and Import DataPump (impdp) utilities. Export dumpfiles created with Export DataPump can only be read by Import DataPump, and export dumpfiles created with the original Export utility cannot be read by the Import DataPump client.
For compatibility rules for the enhanced utilities Export Data Pump (expdp) and Import Data Pump (impdp) which were introduced in Oracle10g, see:
Note:553337.1 "Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions"

3. Data Dictionary Views.

3.1. Script catexp.sql
Before using the Export or Import utility, the Data Dictionary has to be prepared with the views that are needed by these utilities. This can be done by running the script:

This script CATEXP.SQL has to be run by the user SYS. The script is called automatically by the CATALOG.SQL script. Both scripts CATEXP.SQL and CATALOG.SQL need to be run only once on a database. Normally, you never need to run it again before you perform an export or import (the errors EXP-24 or IMP-23 indicate that the views are not correct anymore).

The script CATEXP.SQL performs the following tasks to prepare the database for an export and/or import:

• create the required export and import views in the data dictionary;
• create the EXP_FULL_DATABASE role and the IMP_FULL_DATABASE role;
• assign all necessary privileges to the EXP_FULL_DATABASE role and the IMP_FULL_DATABASE role;
• assign EXP_FULL_DATABASE and IMP_FULL_DATABASE to the DBA role;
• update table sys.props$ with the version of the export and import views (see the notes at the end of the article).

3.2. Script catexp7.sql
When creating an Oracle7 export file from an Oracle8/8i database by running the Oracle7 Export client against the Oracle8/8i server (see details below), the user SYS must have run the CATEXP7.SQL script on the Oracle8/8i database:

This script creates the export views that make the database look, to Export, like an Oracle7 release database. This means that the views will not see any new Oracle8/8i specific objects, so the export file can be imported without problems in the Oracle7 database.

Keep in mind that both the scripts CATEXP.SQL and CATEXP7.SQL only need to be run once: CATEXP.SQL creates the exu8% views which are used by the Oracle8/8i/9i/10g/11g Export clients, and CATEXP7.SQL creates the exu7% views which are used by the Oracle7 Export clients. So if both scripts have been run by SYS, you do not need to run them again.

The procedure mentioned above, also applies for creating an Oracle6 export dump file from an Oracle7 database. In this case the user SYS must have run the CATEXP6.SQL script on the Oracle7 database.

Note that running an Oracle7 Export client against Oracle9i database is not supported: in a downgrade scenario, we only support one functional release back. This also means that running an Oracle8 Export client against an Oracle10g database is not supported (currently, it is only supported to run the Oracle8i 8.1.7 Export client or higher against the Oracle10g database, or to run the Oracle9i 9.2.0 Export client or higher against the Oracle11g database). For details, see also:
Note:207303.1 "Client / Server / Interoperability Support Between Different Oracle Versions"

4. Database Migration with a full database export/import.

4.1. Limitations of a direct upgrade method.
When upgrading or migrating a database, you can use the 'Database Upgrade Assistant', or you can perform a manual upgrade, or you can use the Export Data Pump and Import Data Pump utilities. If the database is migrated to Oracle8i, Oracle9iR2, Oracle10g, or Oracle11g there are limitations regarding a direct upgrade with the Database Migration Assistant or the Manual Upgrade (for details, see the Upgrade Guide). These limitations do not exist for a migration with the Export and Import Utilities. The Export and Import utilities physically copy data from the source database to a new target database. Be aware though that this also means that the time requirements for upgrading a database with Export/Import are higher than for the other upgrade methods (i.e.: slowest migration method).

4.2. Patchsets.
When migrating a database with a full database Export/Import (Data Pump) we recommend that both the original source database and the new target database have the latest patchset installed. This means:
- for Oracle 7.3.2 source database: have the 7.3.2.3.0 patchset installed;
- for Oracle 7.3.3 source database: have the 7.3.3.6.0 patchset installed;
- for Oracle 7.3.4 source database: have the 7.3.4.5.0 patchset installed;
- for Oracle 8.0.3 source database: have the 8.0.3.2.0 patchset installed;
- for Oracle 8.0.4 source database: have the 8.0.4.4.0 patchset installed;
- for Oracle 8.0.5 source database: have the 8.0.5.2.1 patchset installed;
- for Oracle 8.0.6 source database: have the 8.0.6.3.0 patchset installed (see: Patch:1645513);
- for Oracle8i Release 1 source database: have the 8.1.5.1.0 patchset installed;
- for Oracle8i Release 2 source database: have the 8.1.6.3.0 patchset installed (see: Patch:1650087) or if withdrawn, the 8.1.6.3.1 or 8.1.6.3.2 patchset;
- for Oracle8i Release 3 source or target database: have the 8.1.7.4.0 patchset installed (see: Patch:2376472);
- for Oracle9i Release 1 source database: have the 9.0.1.4.0 patchset installed (see: Patch:2517300) or the 9.0.1.5.0 patchset for iAS infrastructure configurations (see: Patch:3301544);
- for Oracle9i Release 2 source or target database: have the 9.2.0.8.0 patchset installed (see: Patch:4547809);
- for Oracle10g Release 1 source or target database: have the 10.1.0.5.0 patchset installed (see: Patch:4505133);
- for Oracle10g Release 2 source or target database: have the 10.2.0.4.0 patchset installed (see: Patch:6810189);
- for Oracle11g Release 1 target database: have the 11.1.0.7.0 patchset installed (see: Patch:6890831).

For details about the latest patchsets and an overview of known issues, see also:
Note:33511.1 "ALERT: Oracle 7.2.3 Support Status and Alerts"
Note:35457.1 "ALERT: Oracle 7.3.2 Support Status and Alerts"
Note:45418.1 "ALERT: Oracle 7.3.3 Support Status and Alerts"
Note:50220.1 "ALERT: Oracle 7.3.4 Support Status and Alerts"
Note:52717.1 "ALERT: Oracle 8.0.4 Support Status and Alerts"
Note:62259.1 "ALERT: Oracle 8.0.5 Support Status and Alerts"
Note:72529.1 "ALERT: Oracle 8.0.6 Support Status and Alerts"
Note:68920.1 "ALERT: Oracle 8i Release 1 (8.1.5) Support Status and Alerts"
Note:93849.1 "ALERT: Oracle 8i Release 2 (8.1.6) Support Status and Alerts"
Note:120607.1 "ALERT: Oracle 8i Release 3 (8.1.7) Support Status and Alerts"
Note:149018.1 "ALERT: Oracle 9i Release 1 (9.0.1) Support Status and Alerts"
Note:189908.1 "ALERT: Oracle 9i Release 2 (9.2) Support Status and Alerts"
Note:263719.1 "ALERT: Oracle 10g Release 1 (10.1) Support Status and Alerts"
Note:316900.1 "ALERT: Oracle 10g Release 2 (10.2) Support Status and Alerts"
Note:454507.1 "ALERT: Oracle 11g Release 1 (11.1) Support Status and Alerts"
Note:880782.1 "ALERT: Oracle 11g Release 2 (11.2) Support Status and Alerts"

5. Basic Compatibility.

5.1. Export client.

Remarks:
1. When creating an export dump file for an import into a higher release database (e.g.: from Oracle8i to Oracle9i), use a version of the Export client that is equal to the version of the source database (= lowest version = Oracle8i in this case). The export fails if you use a higher release export version.
For example, if you use the export 9.0.1 utility the export data from an 8.1.7 database, you will get the errors:

Solution: use the lowest release export utility (8.1.7 in this case).

2. When creating an export dump file for an import into a lower release database (e.g.: from Oracle9i to Oracle8i), use a version of the Export client that is equal to the version of the target database (= lowest version = Oracle8i in this case).

• When creating an Oracle6 export file from an Oracle7 database by running the Oracle6 Export client against the Oracle7 server, the user SYS must first run the CATEXP6.SQL script on the Oracle7 database. This script creates the export views that make the database look, to Export, like an Oracle6 release database.
• When creating an Oracle7 export file from an Oracle8/8i database by running the Oracle7 Export client against the Oracle8/8i server, the user SYS must first run the CATEXP7.SQL script on the Oracle8/8i database. This script creates the export views that make the database look, to Export, like an Oracle7 release database.
• When creating an Oracle8/8i export file from an Oracle9i database by running the Oracle8/8i Export client against the Oracle9i server, you do not have to take any special steps. I.e., you do not need to run the 8i version of catexp.sql against the 9i database. You only need to run the 8i exp executable against the 9i database. Be aware that several Oracle9i specific features are not supported/exported (e.g. no LOBs and objects are exported when using DIRECT=YES). See the "Oracle9i Database Utilities" manual for a complete list of these restrictions).

3. The original Export client is desupported for general use as of Oracle Database 11g. Oracle recommends that you use the new Data Pump Export and Import utilities in Oracle11g and above, except if you need to import files into Oracle11g that were created using the original Export utility (exp), or if you want to export files from Oracle11g that need to be imported using the original Import utility (imp), e.g. importing into an 9.2.0.8 target database.

5.2. Patchsets.
With some patchsets the data dictionary export views are also changed. As a result it will not be possible to run this patched Export client against an unpatched database. Therefore the same basic rule also applies to the patchset release: Export the data with the Export client of the lowest database version involved.

Example 1: an export with 9.2.0.5 export client from a 9.2.0.1 database can give the errors:

Solution: To successfully export from a 9.2.0.1 database (select status, version, comp_id from dba_registry;) that is located in a 9.2.0.1 $ORACLE_HOME (select * from v$version;), use the 9.2.0.1 Export client, and not the 9.2.0.2 or higher Export client.

Example 2: An export with 9.2.0.6 Export client from a 9.2.0.5 database will give the following errors on the export a partitioned table with a subpartition template:

Solution: use the 9.2.0.5 Export client if your intention is to import back into a 9.2.0.5 or any higher release database.

Example 3: An export with 10.2.0.4 Export client from a 10.2.0.3 source database will give the following errors on the export of operators:

Solution: use the 10.2.0.3.0 Export client to export data from an 10.2.0.3.0 source database and use the 10.2.0.4.0 Import client to import the data into the 10.2.0.4.0 target database.

5.3. Import Client.

6. Limitations.

6.1. Export/Import features and limitations.
The following limitations apply to the Export and Import clients (see details in the matrix below):

1. Oracle export dump files can only be read by the Oracle Import client because these dump files are stored in a special Oracle-binary format.
2. Every export dump file is importable into all future major, patch, and maintenance releases of Oracle as long as the original import client is part of that shipment. There are currently no plans to desupport the original import client in a future release.
3. The export dump files cannot be read by Import clients of previous maintenance releases and versions. So, a version 10gR2 (10.2.0.x) export dump file cannot be imported by a version 10gR1 (10.1.0.x) Import client (possible errors: ORA-2248), and a version 9 export dump file cannot be imported by a version 8i Import client (possible errors: IMP-10 and IMP-21), and a version 8 export dump file cannot be imported by a version 7 Import client (possible errors: IMP-69 and IMP-21).
   In all these cases, the import will terminate with the error IMP-0 "Import terminated unsuccessfully".
4. Import can read export dump files created by Export release 5.1.22 and higher (up to same version).
5. Import cannot read export dump files created by the Export client of a higher maintenance releases or versions. So, a release 8.1 export dump file cannot be imported by a release 8.0 Import client, and a version 8 export dump file cannot be imported by a version 7 Import client.
6. The Oracle6 (or earlier) Export client cannot be used against an Oracle8 or Oracle8i or higher release database.
7. Whenever a lower version Export client runs with a higher version of the Oracle Server, any categories of database objects that did not exist in the lower version are excluded from the export. For example, partitioned tables are not exported by the version 7 Export client. If you need to move a version 8 partitioned table to a version 7 database, then first reorganize the table into a non-partitioned table.
8. The original Export client is desupported for general use as of Oracle Database 11g. Oracle recommends that you use the new Data Pump Export and Import utilities in Oracle11g and above, except if you need to import files into Oracle11g that were created using the original Export utility (exp), or if you want to export files from Oracle11g that need to be imported using the original Import utility (imp), e.g. importing into an 9.2.0.8 target database.

6.2. Import data into an older release (desupported) target database.

Remarks:
1) Import can read export dump files created by Export release 5.1.22 and higher (up to same version).
2) An Oracle5 or Oracle6 export dump and an Oracle7 Import client: see the Oracle Utilities Manual, Chapter 2 "Import" for special considerations to keep in mind.
3) To export from Oracle7 for import into an Oracle6 database: user SYS must run script CATEXP6.SQL on the Oracle7 database first (this script needs to be run only once to have the version6 views been created).
4) To export from Oracle8, Oracle8i for import into an Oracle7 database: user SYS must run script CATEXP7.SQL on the Oracle8/8i database first (this script needs to be run only once in order to create the version7 views).
5) Only the terminal Oracle8 release (8.0.6) was supported against an Oracle9i database: to export from Oracle9i and import into 8.0.6, use the Oracle8 Export client.

6.3. Import data into Oracle8i or higher release database.

Remarks:
1) Import can read export dump files created by EXPORT release 5.1.22 and higher (up to same version).
2) An Oracle5 or Oracle6 export dump and an Oracle8 or Oracle8i or Oracle9i Import: see the Oracle Utilities Manual, Chapter 2 "Import" for special considerations to keep in mind.
3) An Oracle7 export dump and an Oracle8 or Oracle8i or Oracle9i Import: check constraints on the DATE columns become invalid when TO_DATE function has not been used in the constraint (this was not required in earlier Oracle versions).
4) To have a better performance and more flexibility when exporting from and importing into Oracle10g and higher release databases, we recommend the usage of the Export Data Pump (expdp) and Import Data Pump (impdp) clients.
5) The original Export client is desupported for general use as of Oracle Database 11g.
*) If EXP920 is used to export from 11g (R1 or R2); then first check the Note 550740.1 Export From 11g using EXP Utility Version 9iR2 Produces Corrupt Export Dump and apply the solution.

7. Examples.

Video - Example: export from 10.2.0.4 and import into 9.2.0.8 (02:21)

7.1. Example: export from 7.3.4 and import into 8.1.7

• use the 7.3.4 Export client to export the data from the 7.3.4 source database,
• use the 8.1.7 Import client to import the data into the 8.1.7 target database.

7.2. Example: export from 9.2.0.8 and import into 9.2.0.8

• use the 9.2.0.8 Export client to export the data from the 9.2.0.8 source database,
• use the 9.2.0.8 Import client to import the data into the 9.2.0.8 target database.

7.3. Example: export from 9.2.0 and import into 8.1.7

• use the 8.1.7 Export client to export the data from the 9.2.0 source database,
• use the 8.1.7 Import client to import the data into the 8.1.7 target database.

7.4. Example: export from 8.1.7.4 and import into 11.1.0.6

• use the 8.1.7.4 Export client to export the data from the 8.1.7.4 source database,
• use the 11.1.0.6 Import client to import the data into the 11.1.0 target database.

7.5. Example: export from 9.2.0 and import into 7.3.4

• Not supported; workaround:
• use an 8.1.7.4 Export client to export the data from the 9.2.0 source database,
• use the 8.1.7.4. Import client to import the data into a temporary 8.1.7.4.0 database,
• only if never run before, run CATEXP7.SQL on the 8.1.7 database in order to create the Oracle7 data dictionary views,
• use the 7.3.4 Export client to export the data from the temporary 8.1.7.4.0 database,
• use the 7.3.4 Import client to import the data into the 7.3.4 target database.

7.6. Example: export from 7.3.4 and import into 11.1.0

• use the 7.3.4 Export client to export the data from the 7.3.4 source database,
• use the 11.1.0 Import client to import the data into the 11.1.0 target database.

7.7. Example: export from 10.1.0.5 and import into 10.2.0.4

• use the 10.1.0.5 Export Data Pump client (expdp) to export the data from the 10.1.0.5 source database,
• use the 10.2.0.4 Import Data Pump client (impdp) to import the data into the 10.2.0.4 target database.

8. Possible error messages.

8.1. EXP-3 (no storage definition found for segment xxx).

Cause: Could not find the storage definitions for cluster/index/table.
Action: Record the error messages and report this as Export internal error.
Remarks: See also Note:274076.1 "EXP-00003 When Exporting From 9.2.0.5.0 or any Higher Release with a Pre-9.2.0.5.0 Export Client"

8.2. EXP-24 (Export views not installed, please notify your DBA).

Cause: The necessary export views were not installed.
Action: Ask the database administrator to install the required export views.

8.3. EXP-37 (Export views not compatible with database version).

Cause: The EXPORT utility is at a higher version than the database version and is thereby incompatible.
Action: Use the same version of EXPORT utility as the database.

8.4. IMP-0 (Import terminated unsuccessfully).

Cause: Error encountered.
Action: Check the preceding error message and take appropriate actions.

8.5. IMP-9 (Abnormal end of export file).

Cause: This is usually caused by an export file generated by an aborted export session.
Action: Check if file was transferred in Binary-mode. If the export file was generated by an aborted export session, retry the export followed by the import, else report this as an Import bug.

8.6. IMP-10 (Not a valid export file, header failed verification).

Cause: Either it is not a file generated by the Export utility or that the file is corrupted.
Action: Check if file was transferred in Binary-mode. If the file was indeed generated by the Export utility, and no compatibility issue
is related, report this as an Import bug.

8.7. IMP-21 (operating system error - error code (xxx)).

Cause: Operating system error. Can occur together with IMP-10 or IMP-69.
Action: Check the OS manual for the error code and take appropriate action. Check for compatibility issues when next errors are IMP-10 or IMP-69.

8.8. IMP-23 (Import views not installed, please notify your DBA).

Cause: Import views not installed.
Action: Notify DBA to install import views.

8.9. IMP-69 (Could not convert to environment national character set's handle).

Cause: Internal error.
Action: Contact Worldwide support.

9. Remarks.

9.1. Privileges.
To use the Export client, you must have the CREATE SESSION privilege on an Oracle database. To export tables owned by another user, you must have the EXP_FULL_DATABASE role enabled. This role is granted to all DBAs.

9.2. Oracle Net, Oracle Net8, and SQL*Net.
To use an older Export client release on a newer database release, you have to use SQL*Net or Net8 or Oracle Net to access this database:

9.3. Direct Path export.
When using an Oracle7 or lower Export client on an Oracle8i or higher release database, you have to use the default CONVENTIONAL path export (i.e.: do not specify DIRECT=YES in the export command statement). A conventional path export uses the SQL SELECT statement to extract data from tables. A direct path export reads the data directly, bypassing the SQL Command Processing layer (evaluating buffer).
Also beware that export does not export rows from tables containing objects and LOB's when you use Export release 8.0 on an Oracle8i or higher release database and have specified a direct path load (DIRECT=Y).

9.4. Export Data Pump and Import Data Pump.
With Oracle10g Release 1 (10.1.0.x) we have introduced the new Export Data Pump (expdp) and Import Data Pump (impdp) utilities. The original Export client is desupported for general use as of Oracle Database 11g. The original Import client will still be provided in order to import export dumpfiles created with the original export client (e.g. when exported from Oracle9i).
For details, see also:
Note:345187.1 "Feature Obsolescence - Original Export 10.2"
Note:553337.1 "Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions"

9.5. Export/Import views revision.
In order to check the revision of the export and import views, you can query the table sys.props$ with the following statement:

The value of the property EXPORT_VIEWS_VERSION has the following meaning:

9.6. NLS_LANG environment variable.
For details about the impact of the environment variable NLS_LANG during an export and import, see the following FAQ:
Note:227332.1 "NLS considerations in Import/Export - Frequently Asked Questions"