Oracle Utilities - Inter-Version Compatibility

Author: Packt Publishing Published on: 14th Jan 2010

In, the previous example a 10g data pump generated an External Table that was transparently read by the 11g release.

Ads

Let's create an 11g data pump External Table named DP_DEPARTMENTS:

Sample Code

create table dp_departments
organization external(
type oracle_datapump
default directory EXTTABDIR
access parameters
(
version '10.2.0'
)
location ('dp_departments.dmp')
)
as
select * from departments
Table created.
SQL> select count(*) from dp_departments
COUNT(*)
----------
27

In the previous example it is important to point out that the VERSION keyword defines the compatibility format.

access parameters ( version '10.2.0' )

If this clause is not specified then an incompatibility error will be displayed.

Sample Code

SQL> select count(*) from dp_departments
select count(*) from dp_departments
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-39142: incompatible version number 2.1 in dump file "/home/oracle/external_table_dest/dp_departments.dmp"
ORA-06512: at "SYS.ORACLE_DATAPUMP", line 19

Now let's use the 10g version to read from it.

Sample Code

SQL> select count(*) from dp_departments
COUNT(*)
----------
27

Ads

The VERSION clause is interpreted the same way as the VERSION clause for the data pump export, it has three different values:

· COMPATIBLE: This states that the version of the metadata corresponds to the database compatibility level.
· LATEST: This corresponds to the database version.
· VERSION NUMBER: This refers to a specific oracle version that the file is compatible with. This value cannot be lower than 9.2.0.