Sunday, July 29, 2007

ORA-00904: "SYS"."DBMS_EXPORT_EXTENSION"."FUNC_INDEX_DEFAULT": invalid identifier

[Problem]

Export: Release 9.2.0.8.0 - Production on Wed Jul 25 10:47:29 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)
About to export specified tables via Direct Path ...
Current user changed to DBAPL
. . exporting table RPT_LOCAL__01 1012208 rows exported
EXP-00056: ORACLE error 904 encountered
ORA-00904: "SYS"."DBMS_EXPORT_EXTENSION"."FUNC_INDEX_DEFAULT": invalid identifier
Export terminated successfully with warnings.

[Root Cause]

It caused by different DB version in the server , as below two version is installed:
DB1 V9.2.0.8 , while DB2 is 9.2.0.5
In operator id 's env , the ORACLE_HOME is point to 9.2.0.8, caused using higher version exp to exp data from lower version db.
[solution]
in the script , explicitly specify the path of exp , i.e:
/software/oradb1/product/9.2.0/bin/exp $RPT_CONNECT@$RPT_DB file=$expfile log=$LOGFILE direct=y recordlength=65535 buffer=10240000 compress=N tables=${tab_name} statistics=none

Today 21-Jan-2009, hit another similar case. get new findings from metalink
Doc ID: 358508.1
.

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.2.0.1
This problem can occur on any platform.

Symptoms

Export fails with:

EXP-00056: ORACLE error 904 encountered
ORA-00904: "SYS"."DBMS_EXPORT_EXTENSION"."FUNC_INDEX_DEFAULT": invalid identifier


Changes

A patchset was recently installed against the database.

Cause

After the patchset installation, the data dictionary components have not been upgraded to the patchset release (e.g. 10.1.0.4)

A patchset (e.g. 10.1.0.4) was applied to an existing ORACLE_HOME install but one or more of the patchset steps were not performed per the patchset readme to bring the database components up to the same version as the patched software as shown in DBA_REGISTRY.

If the database components versions do not match the software and the running instance, then the exp utility fails as it finds an earlier version of the exp packages in the database.

Solution

Follow the Post-Installation Tasks section of the Oracle Database Server Patch Set Notes to run the catpatch.sql script with the database started in migrate mode and then retry the export.

Connect to the database as SYSDBA and run:

SQL> STARTUP MIGRATE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> SPOOL OFF
SQL> SHUTDOWN
SQL> STARTUP


No comments:

Post a Comment