Can export Oracle data - ORA-00904 errors.

[Gino Castoldi_2]

Hi,

HP-UX 11.0 Oracle 8.1.7.3.0 VPO 6.14

I installed the Oracle Patch 8.1.7.4.
I checked the log files and it all looks ok.
I ran a 'oracle$ relink all' afterwards.
When I attempt to run a Oracle exp it fails
with Oracle 904 errors and the export fails.

Any ideas on how to resolve this issue?

10 points to any good answer.
TIA, Gino

[Michael Schulte zur Sur · Accepted solution]

Hi,

ora-00904 is an invalid column name. If you have access to metalink, look here:
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=232708.1

greetings,

Michael

[Gino Castoldi_2]

Hi,

I don't have access to MetaLink unfortunately. Can you post a copy of the
document here?

10 points to any good answer.
TIA, Gino

[Michael Schulte zur Sur]

Hi,

here it is.

greetings,

Michael

[Elena Leontieva]

Gino,

I found this on Metalink:

fact: Oracle Server - Enterprise Edition
symptom: ORA-00904: Invalid column name
symptom: EXP-00008: ORACLE error %s encountered
cause: The package is missing, invalid or corrupt



fix:

How to handle ORA-00904 errors on Export
========================================

When running full database export the following errors will be returned:

exp80 user/password full=y file=D:\DB-Backup\ORCL\expORCL.dmp
log=D:\DB-Backup\ORCL\expORCL.log consistent=y buffer=40960000

EXP-00008: ORACLE error 904 encountered
ORA-00904: invalid column name




To get a clue what has force these ORA-00904 message, follow the following
steps:

1. Connect as privileged user and run the following command inside an
SQLPLUS session:

SQL> alter system set events='904 trace name errorstack';

-------------------------------- Note ------------------------------------

This event will work starting with Oracle8i. If running a Oracle database
version below Oracle8i, you'll get ORA-02065 errors returned, when
specifying this event. In Oracle versions below 8i you'll have to insert
the following entry into INIT.ORA

event="904 trace name errorstack"

--------------------------------------------------------------------------

2. Retry the FULL export. When the ORA-00904 occurs, a trace file will be
written to the storage location specified by the INIT.ORA parameter
'user_dump_dest'. As soon as the ORA-00904 has been returned to your
export session, you can abort the export and examine the trace information.

3. Disable event tracing by running the following command:

SQL> alter system set events='904 trace name errorstack off';

On Oracle versions below 8.1.5 return the event entry from INIT.ORA

4. Examine the trace file:

*** SESSION ID:(9.3) 2001.11.21.15.28.00.494
*** 2001.11.21.15.28.00.494
ksedmp: internal or fatal error
ORA-00904: invalid column name
Current SQL statement for this session:
SELECT fname,fsize,maxextend,inc FROM sys.exu8fil WHERE tsid = :1
===========

a) a problem with object EXU8FIL has been detected
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
In this example, there's a problem with the EXE8FIL view. To examine,
whether this object exist, run the following command:

SQL> select owner,object_name,object_type,object_id,status
from dba_objects
where object_name='EXU8FIL';

If the problem is on the EXU8FIL object you simply can recreate the
object by running the CATEXP.SQL script.

b) a problem with object DBMS_JAVA or DBMS_JAVA_TEST
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ksedmp: internal or fatal error
ORA-00904: invalid column name
Current SQL statement for this session:
SELECT SYNNAM, DBMS_JAVA.LONGNAME(SYNNAM), DBMS_JAVA.LONGNAME(SYNTAB), ...

On systems where the Java Server has been installed the trace file could
contain information on a failing select statement on DBMS_JAVA packages.

For reference on fixing errors relating to DBMS_JAVA, please follow
<1017276.102>.


.

[Michael Schulte zur Sur]

Hi,

here is another thing, which may apply to you.

greetings,

Michael

[Indira Aramandla]

Hi Gino,

If this error (ORA-904 invalid column name) occurs after applying a patch, this would probably fix the problem.

1. First, run CATEXP as INTERNAL. Then retry the export. Often times this will fix the problem and then export will run without error.

The reason being a problem with object EXU8FIL view has been detected. To examine, whether this object exist, run the following command:

SQL> select owner,object_name,object_type,object_id,status
from dba_objects
where object_name='EXU8FIL';

If the problem is on the EXU8FIL object you simply can recreate the object by running the CATEXP.SQL script.


IA

[T G Manikandan]

The problem is with the exp/imp objects not getting properly initialized.

Running catexp.sql from $ORACLE_HOME/rdbms/admin should resolve the problem

[T G Manikandan]

It is fine that the upgrade was made.

But did you run catalog.sql and catproc.sql scripts.

This should re-initialize the objects.

This should be definitely done.

Please make sure you run this after the upgrade.

Thanks

[Yogeeraj_1]

hi Gino,

Most likely you have not used all the steps outlined in the patch installation guide. The export views are stale -- causing this error ;)

Try running the current version of catexp.sql found in $ORACLE_HOME/rdbms/admin
and retrying the operation.

This should be solution to your problem.

good luck.

regards
Yogeeraj

[Gino Castoldi_2]

Hi,

I ran "catexp.sql" and "relink all" and that fixed the problem.

svrmgrl>@?/rdbms/admin/catexp.sql

oracle$ relink all

Thank you again for your help.
Gino