ORA-00904: “MAXSIZE”: invalid identifier

I was trying to export from a database. I use Oracle 11g Release 11.2.0.3. My export activity was terminated due to this error:

EXP-00008: ORACLE error 904 encountered
ORA-00904: “MAXSIZE”: invalid identifier
EXP-00000: Export terminated unsuccessfully

This was what I got from Oracle Support:

Applies to:

Oracle Server – Enterprise Edition – Version: 10.1.0.2 to 10.2.0.4 – Release: 10.1 to 10.2
Information in this document applies to any platform.
***Checked for relevance on 22-SEP-2011***

Symptoms

  • ORA-904 occurs when using a release 11g export client to export from release 10g database.
    Export succeeds when using any 10g export client (10.2.0.1 … 10.2.0.4).

Example:
Using export client (11.1.0.7) to export from 10.2.0.4 database
exp scott/****** file=d:\exp.dmp
Export: Release 11.1.0.7.0 - Production on Fri Oct 2 10:14:24 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 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 SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS
EXP-00008: ORACLE error 904 encountered
ORA-00904: "MAXSIZE": invalid identifier
. . exporting table DEPT
EXP-00008: ORACLE error 1003 encountered
ORA-01003: no statement parsed
. . exporting table EMP
EXP-00008: ORACLE error 904 encountered
ORA-00904: "MAXSIZE": invalid identifier
. . exporting table SALGRADE
EXP-00008: ORACLE error 1003 encountered
ORA-01003: no statement parsed
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
EXP-00008: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00024: Export views not installed, please notify your DBA
EXP-00000: Export terminated unsuccessfully

  • Analysis shows this occurs when the following query is run by export client:

ksedmp: internal or fatal error
ORA-00904: "MAXSIZE": invalid identifier
Current SQL statement for this session:
SELECT INIEXT, SEXT, MINEXT, MAXEXT, PCTINC, BLOCKS, LISTS, GROUPS, EXTENTS, PCACHE, TS_TYPE, TSNAME, ISONLINE, BLOCKSIZE, HSCOMPRESS, MAXSIZE FROM SYS.EXU9STOU WHERE FILENO = :1 AND BLOCKNO = :2 AND TSNO = :3

Cause

When export any objects export client run a query agains dictionary view SYS.EXU9STOU to gather storage parameters about object segments.

Since release 11g this view has been updated by a new column MAXSIZE. When using the release 11g export client the utility expect the MAXSIZE column SYS.EXU9STOU of source database.
Given the fact that this column is unavailable in 10g the ORA-904 is reported.

11g
---
SQL> descr SYS.EXU9STOU;
Name                          Null?    Type
----------------------------- -------- ------------------
OWNERID                       NOT NULL NUMBER
TSNO                          NOT NULL NUMBER
FILENO                        NOT NULL NUMBER
BLOCKNO                       NOT NULL NUMBER
INIEXT                        NOT NULL NUMBER
SEXT                          NOT NULL NUMBER
MINEXT                        NOT NULL NUMBER
MAXEXT                        NOT NULL NUMBER
PCTINC                        NOT NULL NUMBER
BLOCKS                        NOT NULL NUMBER
LISTS                                  NUMBER
GROUPS                                 NUMBER
EXTENTS                       NOT NULL NUMBER
PCACHE                                 VARCHAR2(7)
TS_TYPE                                NUMBER
TSNAME                                 VARCHAR2(30)
ISONLINE                               NUMBER
BLOCKSIZE                              NUMBER
HSCOMPRESS                             NUMBER
MAXSIZE                                NUMBER

Solution

  • Please use the release 10g export client to export from a 10g database.
  • Once the export dump has been created you’d have to use the release 11g import client to import into your 11g database.

    To solve this use Oracle 10g client to export the Oracle 10g database and then use the 11g client to import into 11g database

    Advertisements

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s