Saturday, January 26, 2013

EXP-00008 and ORA-00904

A colleague tried to use an Oracle 10 Windows Vista client to export a schema from an Oracle 11.1.0.6.0 database. The export failed and he came to me for help. I have reproduced the error below in a Command Prompt session:

C:\Users\j0294094>exp parfile=paramfile
 
Export: Release 10.2.0.4.0 - Production on Thu Dec 27 16:06:55 2012
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P15 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user CBS
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user CBS
About to export CBS's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export CBS's tables via Conventional Path ...
Etc
Etc
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
EXP-00008: ORACLE error 904 encountered
ORA-00904: "OLEVEL": invalid identifier
EXP-00000: Export terminated unsuccessfully
 
C:\Users\j0294094>

I believe he had this problem because the export utility was on a lower version than the database. One possible solution is to run the export on the UNIX server hosting the database, where the client and the database versions are the same. As you can see below, this runs successfully. However, I’m not sure if this is the best way forwards as Oracle no longer supports export for general use. I’m going to suggest to the developer that he considers using datapump instead:

Solaris > exp parfile=paramfile
 
Export: Release 11.1.0.6.0 - Production on Thu Dec 27 16:34:57 2012
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P15 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user CBS
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user CBS
About to export CBS's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export CBS's tables via Conventional Path ...
Etc
Etc
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
Solaris >

No comments: