oracle 10.1.0.2 exp 后,imp 到10.2.0.x imp-00008错误

经查,这个错误主要是由于10.1.0.2的bug导致的.

通过metalink查以下文档:

Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions [Video] [ID 553337.1]

在10g以后,尽量就不用使用EXP,IMP.

而直接使用expdp,impdp比较合适

还可以参考的文档有:

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

In this Document
Purpose
Scope and Application
Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions [Video]
1. Summary.
2. Introduction.
3. Basic Data Pump Compatibility.
4. Export Data Pump Compatibility.
5. Import Data Pump Compatibility.
6. Database Migration with a Full Database Data Pump Export/Import.
7. Examples.
8. Possible Error Messages.
8.14. ORA-904 (invalid identifier ORIGINAL_OBJECT_NAME).
9. Known Defects.
@ 10. For Support: Enhancement Requests.
References

Applies to:

Purpose

Scope and Application

The article is intended for users of the Oracle10g and Oracle11g databases who wish to use Export Data Pump (expdp) and Import Data Pump (impdp) to export data from an Oracle database release x and import this data into an Oracle database release y. The article gives information how to create an Export Data Pump dumpfile that can be imported into a higher or lower release database, and how the compatibility applies when Data Pump is used over a database link.
For the compatibility of the original export and import clients (exp resp. imp), see:
Note:132904.1 "Compatibility Matrix for Export & Import 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.
• Database compatibility level = the value of init.ora/spfile parameter COMPATIBLE.
• Data Pump client version = the release version of the Data Pump client (software version).
• Database version = the release version of the ORACLE_HOME (= version of database dictionary).

Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions [Video]

1. Summary.

The following are the most important guidelines regarding Data Pump compatibility:

1. The compatibility level of the Data Pump dumpfile set is determined by the compatibility level of the source database.

2. Use the Export Data Pump client (expdp) that matches the version of the source database (up to one major version lower expdp client can be used, but this is not recommended).

3. Use the Import Data Pump client (impdp) that matches the version of the target database (up to one major version lower impdp client can be used, but this is not recommended).

4. Use the Export Data Pump parameter VERSION in case the target database has a lower compatibility level than the source database.

5. Transferring data over a database link is supported even if the compatibility level of the (remote) source database differs from the (local) connected database (up to one major version difference is supported).

6. Import Data Pump can always read Export Data Pump dumpfile sets created by older versions of the database.

7. Export dumpfiles created with the Export Data Pump client (expdp) cannot be read by the original Import client (imp).

8. Export dumpfiles created with the original Export client (exp) cannot be read by the Import Data Pump client (impdp).

Overview of Data Pump dumpfile compatibility.

Overview of Data Pump client/server compatibility.

For details about generic interoperability between Oracle client and server versions, see also:
Note:207303.1 "Client / Server / Interoperability Support Between Different Oracle Versions"

Overview of Data Pump dumpfile set file versions.

Overview of Data Pump client/feature compatibility.

2. Introduction.

2.1. Data Pump.With Oracle10g Release 1 (10.1.0.x) we have introduced the new Export DataPump (expdp) and Import DataPump (impdp) utilities. These utilities have a better performance and increased flexibility when compared to the original export (exp) and import (imp) clients. All Data Pump Export and Import processing, including the reading and writing of dump files, is done on the system (server) selected by the specified database connect string.
With the Oracle Export Data Pump and Import Data Pump utilities you can transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations. With Export Data Pump the object definitions and table data can be extracted from an Oracle database and stored in an Oracle binary-format export Data Pump dumpfile located on the server.
The dumpfile set can then be transferred using FTP in binary mode or physically transported to a different site. With the Import Data Pump client the object definitions and table data can be read from the dumpfile set and then inserted into the Oracle database.

2.2. How to determine the database compatibility level ? To determine the compatibility level of the database, run the following in SQL*Plus:

When checking for compatibility, Data Pump examins the major database release number and the database maintenance release number (i.e.: 11.1 in the example output above).
For details about version numbers, see also:
Note:39691.1 "VERSION NUMBER - Oracle version numbers explained"

2.3. How to determine the software version of the database and the Data Pump client ? The software version of the Export Data Pump and Import Data Pump clients can be obtained by invoking the utilities with the parameter HELP=Y. The banner of the help page shows the version of the utility:

For details, see also:
Note:175627.1 "Export-Import iSR - How to Find the Database and Export/Import Version"

2.4. How to obtain the version of an export Data Pump dumpfile ? Every export dumpfile starts with a header (usually 4 kb in size) which contains details about the dumpfile. Starting with Oracle10g Release 2 (10.2.0.1.0), this header information can be extracted from the dumpfile by calling the procedure DBMS_DATAPUMP.GET_DUMPFILE_INFO.
Possible output based on an example procedure SHOW_DUMPFILE_INFO:

In the example above, the dumpfile set has the compatibility level 11.1 (11.01.00.00.00) and is using a file version format 2.1. This dumpfile set can only be imported into a database with the same or a higher compatibility level.
For details, and the example procedure SHOW_DUMPFILE_INFO which can be called to obtain the export dumpfile header details, see:
Note:462488.1 "How to Gather the Header Information and the Content of an Export Dumpfile ?"

3. Basic Data Pump Compatibility.

3.1. Data Pump dumpfile file version versus Data Pump job version.When an Export Data Pump dumpfile set is created, we store information in the header block of each dumpfile. Besides the version of the Data Pump job, we also store the version of the dumpfile. The version of the dumpfile determines the internal structure of the dumpfile:

• In Oracle10g Release 1 we create an Export Data Pump dumpfile with version 0.1.
• In Oracle10g Release 2 we create an Export Data Pump dumpfile with version 1.1.
• In Oracle11g Release 1 we create an Export Data Pump dumpfile with version 2.1.

@ For Support:@ .

Note that the version of Data Pump dumpfile set is used internally to keep track of Data Pump specific features that result in a change of the internal structure of the dumpfile.
It is not possible to create a dumpfile with a higher version, e.g.: a 10.2.0.4.0 source database cannot create a version 2.1 dumpfile because that dumpfile version has a structure that was introduced in Oracle11g. It is possible though to create a dumpfile with a lower version, e.g.: a 10.2.0.4.0 source database can also create a version 0.1 dumpfile by specifying the Export Data Pump parameter VERSION (see section 4.2. "Export Data Pump parameter: VERSION" below). The reason why you would do this is to make the dumpfile set compatible with the lower release target database so it can be imported.

With the VERSION parameter you can specify the Data Pump job version and indirectly control the version of the dumpfile set. E.g.: if you specify VERSION=10.1 then Data Pump will create a dumpfile set that can be imported into an Oracle10g Release 1 database (dumpfile version: 0.1). When specified, you also determine which version of the objects will be exported. Database objects or attributes that are incompatible with the specified version will not be exported.

During the initial stage of an Import Data Pump job, the header block of each file in the dumpfile set is examined. We compare the value of the dumpfile version to the server's current file_version to make sure that the version of the dumpfile is equal to or less than that of the server.

• Oracle10g Release 1 can import from a Data Pump dumpfile set with the file version 0.1.
• Oracle10g Release 2 can import from a Data Pump dumpfile set with the file version 0.1 or 1.1.
• Oracle11g Release 1 can import from any Data Pump dumpfile set.

An overview of the Data Pump dumpfile versions:

3.2. Data Pump server versus Data Pump clients.Data Pump is server based and not client based. This means that most Data Pump specific defects will be fixed on the server side (changes in packages in source and target database) and not on the client side (Export or Import Data Pump client). If a Data Pump defect that occurs during an import job is fixed in a later patchset (e.g. 10.2.0.4.0) and the target database is still on the base release (10.2.0.1.0) then the defect will still occur when importing with a 10.2.0.4.0 Import Data Pump client into this 10.2.0.1.0 target database. The same applies to export jobs. It is therefore recommended that both the source database and the target database have the latest patchset installed.

3.3. Data Pump dumpfiles versus original export dumpfiles.

For details about the compatibility of the original export and import clients (exp resp. imp), see:
Note:132904.1 "Compatibility Matrix for Export & Import Between Different Oracle Versions

4. Export Data Pump Compatibility.

4.1. Export Data Pump Compatibility Details.

Client/server compatibility:

Remarks:
1. Connecting with a lower version Export Data Pump client (e.g. 10.2.0.4.0) to a higher version source database (e.g. 11.1.0.6.0) is possible, but not recommended.

• We support a compatibility between the database and the Data Pump client where the database may have a higher version with a maximum difference of one major database release. This means that any 10.x Data Pump client can start a Data Pump job on any higher release database (up to 11.x.). For example, you can use a 10.1.0.2.0 Export Data Pump client to start an export Data Pump job on an 11.1.0.6.0 database.
• If the source database had COMPATIBLE=11.1.0, then the resulting dumpfile set will have the same compatibility level as the source database, even if the Export Data Pump job was started with a 10.1.0.2.0 or 10.2.0.4.0 Export Data Pump client.
• It is not recommended to connect with a lower version Export Data Pump client (e.g. 10.2.0.4.0) to a higher version source database (e.g. 11.1.0.6.0).
  • If you are connecting with an older Export Data Pump client, certain Export Data Pump features may be unavailable. For example, if you connect with a 10.2.0.4.0 Export Data Pump client to an 11.1.0.6.0 database, an error will occur if you have also specified COMPRESSION=all because the value 'all' from the COMPRESSION parameter is not recognized by the 10.2.0.4.0 Export Data Pump client.
  • Certain Data Pump client specific defects may not have been fixed in the earlier release of the Export Data Pump client. Note however, that most Data Pump defects are fixed on the server side (source database) and not on the client side (export client).

2. Connecting with a higher version Export Data Pump client (e.g. 11.1.0.6.0) to a lower version source database (e.g. 10.2.0.4.0) is not supported. Attempting to run such a job, will fail with UDE-18 (Data Pump client is incompatible with database version 10.2.0.4.0).

• In exception to the above: within the same maintenance release (e.g. 10.2.0), it is possible to connect with an Export Data Pump client of a later patchset (e.g. 10.2.0.3.0) to a lower version source database (e.g. 10.2.0.1.0).
• Note that Data Pump is server based and not client based. This means that most Data Pump specific defects are fixed on the server side (source database) and not on the client side (export client). If a Data Pump defect is fixed in a later patchset (e.g. 10.2.0.4.0) and the source database is still on the base release (10.2.0.1.0) then the defect will still occur when exporting with a 10.2.0.4.0 Export Data Pump client from this 10.2.0.1.0 source database.
• Beware that there is a known defect when exporting with a 10.2.0.4.0 Export Data Pump client from a 10.2.0.3.0 or lower release database. For details, see also section 9.4. "Bug:7489698 - Schema Export with 10.2.0.4.0 expdp client from 10.2.0.3.0 or lower source fails".

3. In a downgrade scenario, perform the Export Data Pump job with the same version Export Data Pump client as the version of the source database (recommended), and specify the VERSION parameter which is set to the lower compatibility level of the target database.

• If the source database already had the same compatibility of the target database (e.g.: an 11.1.0.6.0 source database with COMPATIBLE=10.2.0, and a 10.2.0.4.0 target database with COMPATIBLE=10.2.0), then it is not required to specify the parameter VERSION=10.2.0 for the Export Data Pump job. However, to avoid confusion we recommend the usage of the VERSION parameter whenever the target database has a lower compatibility or release number.
• See also section 4.2. "Export Data Pump parameter: VERSION" below.

4. If the source database is a read-only database, then an Export Data Pump job that is started on that database will fail because Data Pump cannot create its so-called Master table. To workaround this, consider the read-only source database as a remote database and start the export Data Pump job on a different local database which has a database link to the remote read-only source database. Specify the NETWORK_LINK parameter when connecting with the Export Data Pump client to that different local database. The data from the remote read-only source database instance is transferred over the database link and written to a dump file set on the connected (local) database instance. See also section 4.3. "Export Data Pump NETWORK_LINK compatibility." below.

4.2. Export Data Pump parameter: VERSIONWith the Export Data Pump parameter VERSION, you can control the version of the dumpfile set, and make the set compatible to be imported into a database with a lower compatibility level. Note that this does not mean that dumpfile set can be used with versions of Oracle Database prior to 10.1.0 because Export and Import Data Pump only work with Oracle Database 10g release 1 (10.1) or later.
When specified, internal Data Pump components like the Metadata Application Program Interface (API) make sure that the metadata objects are comptible with the specified version. As a result of this you have the ability to determine which version of the objects will be exported. Database objects or attributes that are incompatible with the specified version will not be exported. For example, tables containing new datatypes that are not supported in the specified version will not be exported.

Remarks:
1. The minimum value of 'version_string' that can be specified is: 9.2 (e.g. if the dumpfile set needs to be imported into a 10.1.0.5.0 database which has COMPATIBLE=9.2 in the init.ora).

2. The maximum value of 'version_string' that can be specified is equal to the release version of the source database. Note that this would be similar to specifying: VERSION=LATEST

3. If you specify a VERSION for an Export Data Pump job that is older than the current database version, certain Export Data Pump features may be unavailable. For example, specifying VERSION=10.1 will cause an error if data compression is also specified for the job (e.g.: COMPRESSION=all), because compression was not supported in 10.1. See also section 8.6. "ORA-39055 (The AAA feature is not supported in version xx.yy.zz)" below.

4. If you specify a VERSION for an Export Data Pump job that is older than the source database version, then a dumpfile set is created that you can import into that older version target database. However, this dumpfile set will not contain any objects that the older specified version does not support. For example, if you export from a version 10.2 database in order to import into a version 10.1 database (i.e. by specifying VERSION=10.1), comments on indextypes will not be exported into the dumpfile set.

5. For an overview of the new database features introduced, see:

• Manual Part No. B10763-01 "Oracle Database Upgrade Guide 10g Release 1 (10.1)", Chapter 5 "Compatibility and Interoperability", Section "Compatibility and Interoperability Issues Introduced in Oracle Database 10g Release 10.1"
• Manual Part No. B14238-01 "Oracle Database Upgrade Guide 10g Release 2 (10.2)", Chapter 5 "Compatibility and Interoperability", Section "Compatibility and Interoperability Issues Introduced in Oracle Database 10g Release 10.2"
• Manual Part No. B28300-02 "Oracle Database Upgrade Guide 11g Release 1 (11.1)", Chapter 5 "Compatibility and Interoperability", Section "Compatibility and Interoperability Issues Introduced in 11g Release 1 (11.1)"

Example:
To create an Export Data Pump dumpfile set from a 11.1.0.6.0 source database with default compatibility level (COMPATIBLE=11.1.0.0.0) and which needs to be imported into a 10.2.0.4.0 target database that has a default compatibility level (COMPATIBLE=10.2.0.4.0), run the export Data Pump job with the 11.1.0.6.0 (or 10.2.0.4.0) Export Data Pump client that connects to the 11.1.0.6.0 source database and provide the Export Data Pump parameter: VERSION=10.2.0.4 (or VERSION=10.2 which means the same).

4.3. Export Data Pump NETWORK_LINK compatibility. Export Data Pump can be started with the NETWORK_LINK parameter which enables an export from a remote (source) database that is identified by a valid database link. The data from the source database instance is transferred over the database link and written to a dump file set on the connected (local) database instance.
The compatibility rules are similar to those of the standard Export Data Pump compatibility:

Remarks: 1. See section 4.1. "Export Data Pump Compatibility Details" above for the standard Export Data Pump compatibility details.

2. Exporting data over a database link that connects to a remote source database with a lower compatibility level is supported.

• We support a maximum compatibility difference between the databases of one major database release. This means that an Export Data Pump job started on a local 11.x database can export data over a database link from any remote 10.x source database. For example, you can use a 11.1.0.6.0 Export Data Pump client that connects to a local 11.1.0.6.0 database and has NETWORK_LINK parameter specified to export data over that database link from a remote 10.1.0.5.0 source database.
• It is not required to specify the VERSION parameter for such an Export Data Pump job as we will automatically use the lowest COMPATIBLE value of the two databases involved, which is in this specific configuration the (remote) source database. The Data Pump dumpfile of previous example will have compatible version 10.1 (dumpfile version: 0.1).
• If the remote source database has a lower compatibility level, certain Export Data Pump features may be unavailable. For example, if the remote source database has COMPATIBLE=10.1.0 then an error will be reported if data compression is also specified for the job (e.g.: COMPRESSION=all), because compression was not supported in 10.1.

3. Exporting data over a database link that connects to a remote source database with a higher compatibility level is also supported.

• We support a maximum compatibility difference between the databases of one major database release. This means that an Export Data Pump job started on a local 10.x database can export data over a database link from any remote 11.x source database. For example, you can use a 10.2.0.4.0 Export Data Pump client that connects to a local 10.2.0.4.0 database and has NETWORK_LINK parameter specified to export data over that database link from a remote 11.1.0.6.0 source database.
• It is not required to specify the VERSION parameter for such an Export Data Pump job as we will automatically use the lowest COMPATIBLE value of the two databases involved, which is in this specific configuration the (local) connected database. The Data Pump dumpfile of previous example will have compatible version 10.2 (dumpfile version: 1.1).
• Due to several defects, Export Data Pump jobs with NETWORK_LINK parameter fail to export from a remote source database with a higher compatibility level. These bugs are fixed in 10.2.0.3.0 and higher, and as a result of that, it is possible to export data over a database link that connects to a remote source database with a higher compatibility level if the release version of the local database is 10.2.0.3.0 or higher. In addition the VERSION parameter has to be specified to workaround two of these defects. Trying to export data over a database link that connects to a remote source database with a higher compatibility level while the release version of the local database is 10.2.0.2.0 or lower will fail due to these defects. See also section 8.9. "ORA-39022 (Database version xx.yy.zz is not supported)" below.

4. When you perform an export over a database link, the data from the remote source database instance is written to dump files on the connected local database instance. The remote source database can be a read-only database.

5. Export Data Pump supports the following types of database links: public, fixed-user, and connected-user. Current-user database links are not supported.

6. If an export operation is performed over an unencrypted network link, then all data is transferred over the database link without any encryption, even if the data was encrypted in the remote source database.

Restrictions:
1. Tables with a LONG column are not supported in an Export Data Pump job with NETWORK_LINK parameter. An ORA-31679 error will be generated and the export will move on to the next table. See also section 8.11. "ORA-31679 (Table data object "aaa"."bbb" has long columns, and longs can not be loaded/unloaded using a network link)" below.

2. Tables with object_type columns are not supported in an Export Data Pump job with NETWORK_LINK parameter. An ORA-22804 error will be generated and the export will move on to the next table. See also section 8.12. "ORA-22804 (remote operations not permitted on object tables or user-defined type columns)" below.

3. When both the NETWORK_LINK and the TABLES parameters are used, then only whole tables can be exported (not partitions of tables). Trying to export a partition of a table over a database link will fail with ORA-39203 (Partition selection is not supported over a network link) or in older releases with ORA-14100 (partition extended table name cannot refer to a remote object). See also section 8.13. "ORA-39203 (Partition selection is not supported over a network link)" below.

Example: An Export Data Pump client (11.1.0.6.0) connects to a local 11.1.0.6.0 database (with COMPATIBLE=11.1.0) with the NETWORK_LINK parameter. The referred database link connects to a remote 10.2.0.4.0 database with COMPATIBLE=10.2.0.4.0 (i.e.: the source database). The Export Data Pump dumpfile set will be written to the server where the 11.1.0.6.0 database is installed, and will have a file version 1.1 and compatibility level of 10.2.0.4.0.

5. Import Data Pump Compatibility.

5.1. Import Data Pump Compatibility Details.

Client/server compatibility:

Remarks:
1. Connecting with a lower version Import Data Pump client (e.g. 10.2.0.4.0) to a higher version target database (e.g. 11.1.0.6.0) is possible, but not recommended.

• We support a compatibility between the database and the Data Pump client where the database may have a higher version with a maximum difference of one major database release. This means that any 10.x Data Pump client can start a Data Pump job on any higher release database (up to 11.x.). For example, you can use a 10.1.0.2.0 Import Data Pump client to start an import Data Pump job on an 11.1.0.6.0 database.
• It is not recommended to connect with a lower version Import Data Pump client (e.g. 10.2.0.4.0) to a higher version target database (e.g. 11.1.0.6.0).
  • If you are connecting with an older Import Data Pump client, certain Import Data Pump features may be unavailable. For example, if you connect with a 10.1.0.5.0 Import Data Pump client to a 10.2.0.4.0 database, an error will occur if TRANSFORM is also specified for the job (e.g.: TRANSFORM=oid:n), because the value 'oid' for the TRANSFORM parameter is not recognized by the 10.1.0.5.0 Import Data Pump client.
  • Certain Data Pump client specific defects may not have been fixed in the earlier release of the Import Data Pump client. Note however, that most Data Pump defects are fixed on the server side (target database) and not on the client side (import client).

2. Connecting with a higher version Import Data Pump client (e.g. 11.1.0.6.0) to a lower version target database (e.g. 10.2.0.3.0) is not supported. Attempting to run such a job, will fail with UDI-18 (Data Pump client is incompatible with database version 10.2.0.4.0).

• In exception to the above: within the same maintenance release (e.g. 10.2.0), it is possible to connect with an Import Data Pump client of a later patchset (e.g. 10.2.0.3.0) to a lower version source database (e.g. 10.2.0.1.0).
• Note that Data Pump is server based and not client based. This means that most Data Pump specific defects are fixed on the server side (target database) and not on the client side (import client). If a Data Pump defect is fixed in a later patchset (e.g. 10.2.0.4.0) and the target database is still on the base release (10.2.0.1.0) then the defect will still occur when importing with a 10.2.0.4.0 Import Data Pump client into this 10.2.0.1.0 target database.
• Beware that there is a known defect when exporting with a 10.2.0.4.0 Export Data Pump client from a 10.2.0.3.0 or lower release database. For details, see also section 9.4. "Bug:7489698 - Schema Export with 10.2.0.4.0 expdp client from 10.2.0.3.0 or lower source fails".

3. Import Data Pump can always import from Export Data Pump dumpfile sets created by older versions of the database. I.e.: an export Data Pump dumpfile set created on 10.1.0.2.0 can be imported into any higher release database.

4. Import Data Pump cannot import dumpfile sets with a higher compatibility level than the target database. I.e.: an export Data Pump dumpfile created with VERSION=COMPATIBLE (default) from a 11.1.0.6.0 source database with COMPATIBLE=11.1.0 can never be imported into a 10.2.0.4.0 target database.

• In exception to the above: new features are usually only introduced with a new main release (like with Oracle10g, Release 2 (10.2.0.1.0)), and not with a patchset (like 10.2.0.4.0 versus the base release of Oracle10g Release 2 (10.2.0.1.0)). Therefore it is possible to import any dumpfile from a main release in all patched versions of the main release (e.g.: import an 10.2.0.4.0 Export Data Pump dumpfile set into a 10.2.0.1.0 target database without specifying the VERSION=10.2.0.1.0 for the Export Data Pump job).

An overview of the Data Pump dumpfile versions:

5.2. Import Data Pump parameter: VERSIONWith the Import Data Pump parameter VERSION, you can control the version of the objects that are loaded into the target database. When specified, internal Data Pump components like the Metadata Application Program Interface (API) make sure that the metadata objects are comptible with the specified version. As a result of this you have the ability to determine which version of the objects will be imported: database objects or attributes that are incompatible with the specified version will not be transferred to the target database.

Remarks:1. It is important to realize that the Import Data Pump parameter VERSION cannot be used to allow a higher version dumpfile to be imported into a lower release database. If the dumpfile needs to be imported into a lower release target database, you have to use the Export Data Pump parameter VERSION to match the compatibility level of the target database. For example, a 11.1.0.6.0 dumpfile from a 11.1.0.6.0 source database with COMPATIBLE=11.1.0 has the version: 2.1. This dumpfile cannot be imported into a 10.2.0.x target database, not even if Import Data Pump was started with VERSION=10.2.0. If the Export Data Pump job would have been done with VERSION=10.2.0 then the dumpfile can be imported into the 10.2.0.x target database because that dumpfile would already have the compatibility of 10.2.0 and the corresponding file version 1.1.

2. The minimum value of 'version_string' that can be specified is: 9.2.

3. The maximum value of 'version_string' that can be specified is equal to the release version of the target database. Note that this would be similar to specifying: VERSION=LATEST

4. If you specify a VERSION for an Import Data Pump job that is older than the version of the dumpfile set, certain Import Data Pump features may be unavailable. For example, specifying VERSION=10.1 will cause an error if transform is also specified for the job (e.g.: TRANSFORM=oid:n), because transforming the Object Identifiers (OID's) of types was not supported in 10.1. See also section 8.6. "ORA-39055 (The AAA feature is not supported in version xx.yy.zz)" below.

5. If you specify a VERSION for an Import Data Pump job that is older than the target database version, then Data Pump will skip the import of any objects from the dumpfile that the older specified VERSION does not support. For example, if you export from a version 10.2 database, and import the data back into another 10.2 database while specifying VERSION=10.1 then comments on indextypes will not be imported into the target database.

5.3. Import Data Pump NETWORK_LINK compatibility.
Import Data Pump can be started with the NETWORK_LINK parameter which enables an import into the connected local target database while extracting the data from a remote source database that is identified by a valid database link. This means that the system to which the Import Data Pump client is connected, contacts the remote source database referenced by the NETWORK_LINK parameter. SQL is directly used to move the data using an INSERT SELECT statement. The SELECT clause retrieves the data from the remote database over the network link. The INSERT clause uses SQL to insert the data into the target database. There are no dump files involved.
The compatibility rules are similar to those of the standard Import Data Pump compatibility:

Remarks:
1. See section 5.1. "Import Data Pump Compatibility Details" above for the standard Import Data Pump compatibility details.

2. Importing data over a database link that connects to a remote source database with a lower compatibility level is supported.

• We support a maximum compatibility difference between the databases of one major database release. This means that an Import Data Pump job started on a local 11.x database can import data over a database link from any remote 10.x source database. For example, you can use an 11.1.0.6.0 Import Data Pump client that connects to a local 11.1.0.6.0 database and has NETWORK_LINK parameter specified to import data over that database link from a remote 10.1.0.5.0 source database.
• It is not required to specify the VERSION parameter for such an Import Data Pump job as we will automatically use the lowest COMPATIBLE value of the two databases involved, which is in this specific configuration the (remote) source database. The version of the objects that are transferred over the database link in previous example is: 10.1.
• If the remote source database has a lower compatibility level, certain Import Data Pump features may be unavailable. For example, if the remote source database has COMPATIBLE=10.1.0 then an error will be reported if TRANSFORM is also specified for the job (e.g.: TRANSFORM=oid:n), because transforming the Object Identifiers (OID's) of types was not supported in 10.1.

3. Importing data over a database link that connects to a remote source database with a higher compatibility level is also supported.

• We support a maximum compatibility difference between the databases of one major database release. This means that an Import Data Pump job started on a local 10.x target database can import data over a database link from any remote 11.x source database. For example, you can use a 10.2.0.4.0 Import Data Pump client that connects to a local 10.2.0.4.0 target database and has NETWORK_LINK parameter specified to export data over that database link from a remote 11.1.0.6.0 source database.
• It is not required to specify the VERSION parameter for such an Import Data Pump job as we will automatically use the lowest COMPATIBLE value of the two databases involved, which is in this specific configuration the (local) target database. The version of the objects that are transferred over the database link in previous example is: 10.2.
• Due to several defects, Import Data Pump jobs with NETWORK_LINK parameter fail to import from a remote source database with a higher compatibility level. These defects are fixed in 10.2.0.3.0 and higher, and as a result of that, it is possible to import data over a database link that connects to a remote source database with a higher compatibility level if the release version of the local target database is 10.2.0.3.0 or higher. In addition the VERSION parameter has to be specified to workaround two of these defects. Trying to import data over a database link that connects to a remote source database with a higher compatibility level while the local target database is 10.2.0.2.0 or lower will fail due to these defects. See also section 8.9. "ORA-39022 (Database version xx.yy.zz is not supported)" below.

4. If the USERID that is executing the Import Data Pump job has the IMP_FULL_DATABASE role on the local target database, then that user must also have the EXP_FULL_DATABASE role on the remote source database.

5. If the remote source database is read-only, then the connected user must have a locally managed tablespace assigned as the default temporary tablespace on the remote source database. Otherwise, the job will fail.

6. Import Data Pump supports the following types of database links: public, fixed-user, and connected-user. Current-user database links are not supported.

7. If an import operation is performed over an unencrypted network link, then all data is transferred over the database link without any encryption, even if the data was encrypted in the remote source database.

Restrictions:
1. Tables with a LONG column are not supported in an Import Data Pump job with NETWORK_LINK parameter. An ORA-31679 error will be generated and the import will move on to the next table. See also section 8.11. "ORA-31679 (Table data object "aaa"."bbb" has long columns, and longs can not be loaded/unloaded using a network link)" below.

2. Tables with evolved object_type columns are not supported in an Import Data Pump job with NETWORK_LINK and TABLE_EXISTS_ACTION parameters (i.e.: in case those tables and types already exist in the target database). An ORA-22804 error will be generated and the import will move on to the next table. See also section 8.12. "ORA-22804 (remote operations not permitted on object tables or user-defined type columns)" below.

3. When both the NETWORK_LINK and the TABLES parameters are used, then only whole tables can be imported (not partitions of tables). Trying to import a partition of a table over a database link will fail with ORA-39203 (Partition selection is not supported over a network link) or in older releases with ORA-14100 (partition extended table name cannot refer to a remote object). The only exception to this is if TRANSPORTABLE=ALWAYS is also specified, in which case single or multiple partitions of a specified table can be imported. See also section 8.13. "ORA-39203 (Partition selection is not supported over a network link)" below.

6. Database Migration with a Full Database Data Pump Export/Import.

6.1. Upgrade Method with Data Pump.
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. The Oracle Data Pump 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). If you use the Data Pump utilities you may have the following benefits:

• defragments the data (you can compress the imported data to improve performance);
• restructures the database (you can create new tablespaces or modify existing tables, tablespaces, or partitions to be populated by imported data);
• facilitates side-by-side testing of the old and new versions of Oracle Database because an entirely new database is created;
• enables the copying of specified database objects or users (you can import only the objects, users, and other items that you wish; this is useful for establishing a test environment for the new software on only a subset of the production data);
• serves as a backup archive (you can use a full database export as an archive of the current database);
• allows the new database to be established on an Operating System or hardware platform that is different from that supporting the database being upgraded;
• network-based Data Pump Import allows the new Oracle database to be directly loaded across the network from the old database being upgraded (no intervening dump files are required).

6.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 before the full database Export Data Pump job resp. Import Data Pump job is started.
This means:
- 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.3.0 patchset installed (see: Patch:5337014) or the 10.2.0.4.0 patchset (see: Patch:6810189);
- for Oracle11g Release 1 source or 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: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"

6.3. Migration to Oracle10g Release 2 (10.2.0.x.0). A direct migration with a full database Export Data Pump job and full database Import Data Pump job is supported from the following versions of the source database.

Note that we also recommend that the latest patchset is installed on the target database. For the 10.2.0.3.0 patchset see: Patch:5337014 and for the 10.2.0.4.0 patchset see: Patch:6810189.

6.4. Migration to Oracle11g Release 1 (11.1.0.x.0).A direct migration with a full database Export Data Pump job and full database Import Data Pump job is supported from the following versions of the source database.

7. Examples.

7.1. Import data into a target database with a higher compatibility level.

Solution:

• Start the export job using the 10.1.0.5.0 Export Data Pump client that connects to the 10.1.0.5.0 source database.
• Transfer the dumpfile set to the server where the target database is located.
• Start the import of the data using the 10.2.0.4.0 Import Data Pump client that connects to the 10.2.0.4.0 target database.

Remarks:
1. Instead of the 10.1.0.5.0 Export Data Pump client, any other lower release Export Data Pump client can be used to start the export job, e.g. a 10.1.0.2.0 Export Data Pump client. However, this is not recommended. The dumpfile set has the same compatibility as the compatible value of the source database (10.1.0), and can be imported into any Oracle10g or Oracle11g database with a compatible value of 10.1.0 or higher. Instead of the 10.2.0.4.0 Import Data Pump client, any other lower release Import Data Pump client can be used to start the import job, e.g. a 10.1.0.5.0 Import Data Pump client that connects to the 10.2.0.4.0 target database. However, this is also not recommended.

2. Note that an alternative solution would be