PL/SQL导出表数据时发生 ORA-00904: "XXXXX": 标识符无效，处理记录

今天在使用PL/SQL developer导出数据表的时候发生了，ORA-00904:"XXXXX": 标识符无效 的错误，很郁闷。
在一个朋友的空间里搜到了日志记录查看的方法。
以下引用了部分摘录，并对自己的操作做一下记录。

1.使用errorstack跟踪错误信息
SQL>alter system set events '904 trace name errorstack ' ;
Systemaltered.

2.重新使用PL/SQL->Tools->ExportTables->Oracle Export 继续导出部分表数据。()
此时错误重新出现，详细的错误信息记录在了
C:\oracle\product\10.1.0\admin\{sid}\udump\{$sid}_ora_num.trc里了。

打开查看才发现我的export到出表的时候，自动加了一个where条件，以前曾经用PL/SQL developer带条件导出过，现在打开发现原来上面这条件还有，真粗心，赶紧删除掉，OK，重新导出一起正常了。
问题就解决了。

3.关闭对错误信息的跟踪
SQL> alter system set events='904 tracename errorstack off';

很感谢 http://xujt82.spaces.live.com/blog/cns!1EB2C64171792704!239.entry这位朋友提供的方法。

http://xujt82.spaces.live.com/blog/cns!1EB2C64171792704!239.entry 

     数据库升级，fullimport通常会导致一些系统的package，function，trigger失效，这可能会导致exp出现问题，很显然，exp备份失败的大部分和数据库升级，迁移有关。正好我前天在做一个full import的数据库迁移，之后新库的exp备份就出现了错误

[oracle@sxapp1 log]$ exp system/abc123 full=y buffer=65536rows=y  file=/home/oracle/fwsms_$backup_date.dmp
About to export theentire database ...
. exporting tablespace definitions
.  about toexport SYSTEM's tables via Conventional Path ...
. . exportingtable            AQ$_INTERNET_AGENTS          0 rows exported
. .exporting table       AQ$_INTERNET_AGENT_PRIVS          0 rows exported
EXP-00056:ORACLE error 904 encountered
ORA-00904:"SYS"."DBMS_EXPORT_EXTENSION"."FUNC_INDEX_DEFAULT": invalid identifier
.. exporting table                           AUD$          0 rowsexported
. . exporting table                    DEF$_AQCALL          0rows exported
EXP-00056: ORACLE error 904 encountered

出现了一大堆的报错信息，exp备份出现问题，我相信大部分人都遇到过烦人的exp报错，
EXP-00056: ORACLEerror 904encountered，简单的提示，我们并没有得到具体的错误信息，那好，想办法跟踪一下看到底什么导致备份失败

1.使用errorstack跟踪错误信息
[oracle@sxapp1 log]$ sqlplus "/ assysdba"

SQL> alter system set events '904 tracename errorstack ' ;

System altered.

2.执行exp脚本，使跟踪能捕捉到详细的错误信息。
[oracle@sxapp1 log]$ expsystem/abc123 full=y buffer=65536 rows=y file=/home/oracle/fwsms_$backup_date.dmp

3.关闭对错误信息的跟踪
SQL> alter system set events='904 trace nameerrorstack off';

3.查看跟踪到的报错信息
[oracle@sxapp1 udump]$ more fwsms_ora_29535.trc
/u01/app/oracle/admin/fwsms/udump/fwsms_ora_29535.trc
Oracle9iEnterprise Edition Release 9.2.0.7.0 - Production
ORA-04068:existing state of packages has been discarded
ORA-04063:package body "SYS.DBMS_EXPORT_EXTENSION" has errors
ORA-06508:PL/SQL: could not find program unit being called
ORA-06512: at line 1
CurrentSQL statement for this session:
BEGIN        :EXEC_STR :=SYS.DBMS_EXPORT_EXTENSION.PRE_TABLE(:OWNER, :TABNAME);      END;

从跟踪文件中可以看出是SYS.DBMS_EXPORT_EXTENSION这个package导致备份失败，定位出具体的错误信息就好办多了，可以google或是从metalink得到答案

4.下面是SYS.DBMS_EXPORT_EXTENSION的解决办法(from metalink)。

1)ORA-4068, ORA-4063, ORA-6508, ORA-6512 DURING EXPORT

2)ORA-19206 EXP-00056 While Taking Export

1)Problem Description:
====================

When you try to do an export, you immediately receive thefollowing error
messages:

ORA-4068 existing state of packages dbms_export_extension
hasbeen discarded"

ORA-4063 dbms_export_extension has errors

ORA-6508 pl/sql could not find program unit being called

ORA-06512
*Cause: Backtrace message as the stack is unwound byunhandled
exceptions.
*Action: Fix the problem causing theexception or write an exception
handler for this condition. Or youmay need to contact your
application administrator or DBA.

Solution Description:
=====================

Use -SELECT OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS WHERESTATUS =
'INVALID' AND OWNER = 'SYS';

From the select statement you should see thatDBMS_EXPORT_EXTENSIONS package
body is invalid.

If the select statement returns more than 1 invalid 'DBMS_?'object it may
indicate a recent upgrade. You may need to just runyour upgrade scripts,
which are dependant on the version to and fromwhich you upgraded. You may
want to try running catalog.sql andcatproc.sql first. If this does not
resolve the issue try issuing thefollowing statement:

ALTER PACKAGE DBMS_EXPORT_EXTENSION COMPILE BODY;

If you receive errors during the statement, type: "SHOW ERRORS"
Thismay give you additional information as to why the package body is not
compiling.Select from user_errors if the 'show errors' does not give you
additionalinformation.

There are two scripts that create the package and package body,they are
dbmspexp.sql and prvtpexp.plb. You can try running thismanually by doing the
following: (please note that you must beconnected internal and should be in
sqldba or server manager.)

>@dbmspexp.sql
>@prvtpexp.plb

If this still does not resolve the issue you may have to relinkPL/SQL.
Perform the following steps in the order given:

1.) shutdown database

2.) login as oracle

3.) cd to ?/rdbms/lib

4.) make -f oracle.mk procopt install

5.) startup database

6.) run catproc.sql from ?/rdbms/admin after connecting internal

If all else fails, deinstall and reinstall PL/SQL. In some cases,this was
the only solution that worked.

If alter compile is not compiling package and the error is
"identifier... must be declared", try copying relevant
files from CD and retry.

For Example: Say you were not able to compiledbms_aq_import_internal
package. You get error:

"Identifier get_current_schema must be declared"

during compilation. Only after copying "prvtaqad.plb" from CD-ROMand rerunning
"catqueue.sql" made the above package valid.

2)Symptom:
---------

During export you receive the following errors:

EXP-00056: ORACLE error 19206 encountered
ORA-19206: Invalidvalue for query or REF CURSOR parameter
ORA-06512: at"SYS.DBMS_XMLGEN", line 83
ORA-06512: at "SYS.DBMS_METADATA", line345
ORA-06512: at "SYS.DBMS_METADATA", line 410
ORA-06512: at"SYS.DBMS_METADATA", line 449
ORA-06512: at "SYS.DBMS_METADATA", line1156
ORA-06512: at "SYS.DBMS_METADATA", line 1141
ORA-06512: atline 1
EXP-00056: ORACLE error 19206 encountered
EXP-00000: Exportterminated unsuccessfully

Cause:
------

XDB installation/dictionary built causes some of the underlyingXML views to be INVALID.
Here the error occurs when dbms_xmlgen triesto access one those invalid views.

Fix:
----

Run the following script that resides under
$ORACLE_HOME/rdbms/admindirectory after XDB dictionary is built
by connecting as user SYS :

catmeta.sql (Creates Object Model of Oracle dictionary)

After running this script, start the export of database again.