今天在使用PL/SQL developer導(dǎo)出數(shù)據(jù)表的時候發(fā)生了，ORA-00904:"XXXXX": 標識符無效 的錯誤，很郁悶。
在一個朋友的空間里搜到了日志記錄查看的方法。
以下引用了部分摘錄，并對自己的操作做一下記錄。
1.使用errorstack跟蹤錯誤信息
SQL>alter system set events '904 trace name errorstack ' ;
Systemaltered.
2.重新使用PL/SQL->Tools->ExportTables->Oracle Export 繼續(xù)導(dǎo)出部分表數(shù)據(jù)。()
此時錯誤重新出現(xiàn)，詳細的錯誤信息記錄在了
C:\oracle\product\10.1.0\admin\{sid}\udump\{$sid}_ora_num.trc里了。
打開查看才發(fā)現(xiàn)我的export到出表的時候，自動加了一個where條件，以前曾經(jīng)用PL/SQL developer帶條件導(dǎo)出過，現(xiàn)在打開發(fā)現(xiàn)原來上面這條件還有，真粗心，趕緊刪除掉，OK，重新導(dǎo)出一起正常了。
問題就解決了。
3.關(guān)閉對錯誤信息的跟蹤
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
數(shù)據(jù)庫升級，fullimport通常會導(dǎo)致一些系統(tǒng)的package，function，trigger失效，這可能會導(dǎo)致exp出現(xiàn)問題，很顯然，exp備份失敗的大部分和數(shù)據(jù)庫升級，遷移有關(guān)。正好我前天在做一個full import的數(shù)據(jù)庫遷移，之后新庫的exp備份就出現(xiàn)了錯誤
[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
出現(xiàn)了一大堆的報錯信息，exp備份出現(xiàn)問題，我相信大部分人都遇到過煩人的exp報錯，
EXP-00056: ORACLEerror 904encountered，簡單的提示，我們并沒有得到具體的錯誤信息，那好，想辦法跟蹤一下看到底什么導(dǎo)致備份失敗
1.使用errorstack跟蹤錯誤信息
[oracle@sxapp1 log]$ sqlplus "/ assysdba"
SQL> alter system set events '904 tracename errorstack ' ;
System altered.
2.執(zhí)行exp腳本，使跟蹤能捕捉到詳細的錯誤信息。
[oracle@sxapp1 log]$ expsystem/abc123 full=y buffer=65536 rows=y file=/home/oracle/fwsms_$backup_date.dmp
3.關(guān)閉對錯誤信息的跟蹤
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導(dǎo)致備份失敗，定位出具體的錯誤信息就好辦多了，可以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.
