Wednesday, February 4, 2009

ORA-39001, ORA-39023 while using datapump export

Issue:
While performing export with data pump utility , i have mentioned version parameter . Let us check the effect of the same:

C:\>expdp.exe system/a@database directory=dexp dumpfile=example.dmp tables=a,b,c VERSION=9.1

OUTPUT
Export: Release 10.2.0.1.0 - Production on Friday, 06 February, 2009 0:34:43
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.

Reason: this value is too high or too low than the compatibility level of the database
For example on a database with compatibility level 10.2 if version is set to 9.1 while exporting then error will return . and the vice versa is also true . Check the following:

If database compatibility is set to 11.1.0.6 and if VERSION is set higher than also you will face the same

C:\>expdp.exe system/a@database1 tables=a,b,c directory=dexp dumpfile=example2.dmp VERSION=11.1.0.7
OUTPUT
Export: Release 11.1.0.6.0 - Production on Friday, 06 February, 2009 0:34:43
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.


TO AVOID:
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 is supported.

Note: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.


Note: You cannot import a dump taken by data pump from 11G to 10G database if you have not mentioned the version parameter while exporting.

thanks,

Have a good day..................

Experience is the child of thought, and thought is the child of action.
Benjamin Disraeli

1 comment:

Sanjeev Kulkarni said...

Hi I am getting the below error:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39087: directory name DIRECTORY is invalid

Could you please help me to resolve this???