Tuesday, February 3, 2009

ORA-39001, ORA-39023: Version 9.1 is not supported.

Problem Description
While data pump export operation, you made an attempt to specify the dumpfile set version by the value of the VERSION parameter and if this value is too high or too low than the compatibility level of the database then data pump export fails with ORA-39001, ORA-39023: Version %s is not supported.

For example on a database with compatibility level 10.2 if version is set to 9.1 while exporting then error will return,
C:\>J:\oracle\product\10.2.0\db_1\BIN\expdp.exe arju/a@orcl directory=d dumpfile=tabs.dmp tables=test VERSION=9.1

Export: Release 10.2.0.1.0 - Production on Wednesday, 04 February, 2009 0:47:55

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39023: Version 9.1 is not supported.

If database compatibility is set to 11.1.0.6 and if VERSION is set higher than it then error returns.

C:\>F:\app\Administrator\product\11.1.0\db_1\BIN\expdp.exe arju/a@san tables=test directory=d dumpfile=test.dmp VERSION=11.1.0.7

Export: Release 11.1.0.6.0 - Production on Wednesday, 04 February, 2009 0:59:46

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39023: Version 11.1.0.7 is not supported.

Solution of the Problem
Give the VERSION parameter appropriately. Don't give it too high and also don't give it less then 9.2 because up to compatibility 9.2 data pump import operation can be done.

I have noticed that on 10.2.0.1 database compatibility if version is specified as 11.1 then error does not returned.

Below is an example.
C:\>J:\oracle\product\10.2.0\db_1\BIN\expdp.exe arju/a@orcl directory=d dumpfile=tabs.dmp tables=test VERSION=11.1.0

Export: Release 10.2.0.1.0 - Production on Wednesday, 04 February, 2009 0:54:52

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "ARJU"."SYS_EXPORT_TABLE_01": arju/********@orcl directory=d dumpfile=tabs.dmp tables=test VERSION=11.1.0
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ARJU"."TEST" 4.976 KB 10 rows
Master table "ARJU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ARJU.SYS_EXPORT_TABLE_01 is:
D:\TABS.DMP
Job "ARJU"."SYS_EXPORT_TABLE_01" successfully completed at 00:55:32

However if you like to import dumpfile set to a higher version then no need to set VERSION parameter because import Data Pump (impdp) can always read Export Data Pump (expdp) dumpfile sets created by older versions of the database. For example, export dumpfile sets of database 10.1.0.5 can easily be imported into database version 11.1.0.6.

In case of import dumpfile set to a lower version always set VERSION parameter.

No comments:

Post a Comment