SAP Note 12826 - ORA-00904 or EXP-00037 during database export

Symptom:

An export executed with the help of the exp Oracle tools terminates with:

EXP-00008: ORACLE error 904 encountered
ORA-00904: invalid column name
ORA-00904: "": invalid identifier
EXP-00000: Export terminated unsuccessfully

EXP-00037: Export views not compatible with database version

In individual cases, an

ORA-01003: no statement parsed

error occurs instead of the ORA-00904 error, but is usually caused by the same problem.

Other terms

exp80, exp73, exp-37, ora-904, 37, 904, ora-1003, 1003

Reason and Prerequisites

The exp utility requires some views that are stored internally in Oracle. If these are not set up correctly or if they are not appropriate to the exp utility used, one of the above-mentioned errors occurs.

The following measures may solve the problem:

1. Reconstruction of the Oracle dictionary.

2. Use of an exp utility suitable for the Oracle release and patch set.

3. UNIX: Relinking of Oracle.

4. Reinstallation of the Oracle software.

Solution

1. Follow the instructions in Note 582427 to ensure that the Oracle data dictionary is constructed correctly.

2. In addition, make sure that the exp utility is suitable for the Oracle release and patch set. You can check the exp release in the first output line by manually calling exp. As a general rule, exp does not work if the release or patch set is newer than the database. An identical release and patch set would be ideal but an exp utility with a release and patch set that is lower than the database can usually be used as well.

3. Stop the database and relink the Oracle software by executing "relink all" with ora or as described in Note 97953.

4. In individual cases, an incorrect installation of the Oracle software may cause the error. It may happen that an old version of the catexp.sql script is still installed. To exclude this, delete the Oracle software completely using the Universal Installer and install it again (Basis Release -> Patch Set -> Bug Fixes, if required).

If none of these solutions have worked and an ORA-00904 error has occurred, you can activate an error stack dump as described in Note 731 to determine which object is causing an ORA-00904 to be issued when the object in question is accessed.

Example:

The generated trace contains the following trigger SQL statement:

SELECT OBJOWN,OBJNAM,POLICY,POLOWN,POLSCH,POLFUN,STMTS,CHKOPT,
ENABLED,SPOLICY FROM SYS.EXU81RLS WHERE OBJOWN= :1 AND OBJNAM= :2

In this case, the termination occurred when Table EXU81RLS is accessed. It was caused by the exp tool addressing a column which does not exist at database level. The column can be determined by comparing the above column names with the result of

DESC SYS.EXU81RLS.

In the example, the SPOLICY column was missing.
In addition, you can check catexp.sql with regard to the object concerned. This file contained the following entry:

CREATE OR REPLACE view exu81rls
(objown,objnam,policy,polown,polsch,polfun,stmts,chkopt,enabled)
...

Surprisingly, the SPOLICY entry was missing here as well, even though the Oracle and the exp version both belong to Release 8.1.7.4. This implies that the catexp.sql script version was not Version 8.1.7.4, in other words, an error must have occurred when Version 8.1.7.4 was installed.
A reinstallation of the complete Oracle software (8.1.7.0 -> 8.1.7.4) finally solved the problem.