ORA-04063: package body "SYS.DBMS_EXPORT_EXTENSION" has errors

資料庫升級，full import通常會導致一些系統的package，function，trigger失效，這可能會導致exp出現問題，很顯然，exp備份就出現了錯誤

$ exp system/abc123 full=y buffer=65536 rows=y  file=/home/oracle/fwsms_$backup_date.dmp
About export entire database ...
. exporting tablespace definitions
.  about to export SYSTEM's tables via Conventional Path ...
. . exporting table            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 rows exported
. . exporting table                    DEF$_AQCALL          0 rows exported
EXP-00056: ORACLE error 904 encountered

出現了一大堆的報 錯資訊，exp備份出現問題，我相信大部分人都遇到過煩人的exp報錯，
EXP-00056: ORACLE error 904 encountered，簡單的提示，我們並沒有得到具體的錯誤資訊。想辦法跟蹤一下看到底什麼導致備份失敗

1.使用errorstack跟蹤錯誤資訊
$ sqlplus "/ as sysdba"

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

System altered.

2.執行exp指令碼，使跟蹤能捕捉到詳細的錯誤資訊。
$ exp system/abc123 full=y buffer=65536 rows=y  file=/home/oracle/fwsms_$backup_date.dmp

3.關閉對錯誤資訊的跟蹤
SQL> alter system set events='904 trace name errorstack off';

  檢視跟蹤到的報錯資訊
$ more fwsms_ora_29535.trc
/u01/app/oracle/admin/fwsms/udump/fwsms_ora_29535.trc
Oracle9i Enterprise 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
Current SQL 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 the following error
messages:

ORA-4068 existing state of packages dbms_export_extension
has been 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 by unhandled
exceptions.
*Action: Fix the problem causing the exception or write an exception
handler for this condition. Or you may need to contact your
application administrator or DBA.

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

Use -SELECT OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS WHERE STATUS =
'INVALID' AND WNER = 'SYS';

From the select statement you should see that DBMS_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 run your upgrade scripts,
which are dependant on the version to and from which you upgraded. You may
want to try running catalog.sql and catproc.sql first. If this does not
resolve the issue try issuing the following statement:

ALTER PACKAGE DBMS_EXPORT_EXTENSION COMPILE BODY;

If you receive errors during the statement, type: "SHOW ERRORS"
This may 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
additional information.

There are two scripts that create the package and package body, they are
dbmspexp.sql and prvtpexp.plb. You can try running this manually by doing the
following: (please note that you must be connected 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 relink PL/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 compile dbms_aq_import_internal
package. You get error:

"Identifier get_current_schema must be declared"

during compilation. Only after copying "prvtaqad.plb" from CD-ROM and 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: Invalid value for query or REF CURSOR parameter
ORA-06512: at "SYS.DBMS_XMLGEN", line 83
ORA-06512: at "SYS.DBMS_METADATA", line 345
ORA-06512: at "SYS.DBMS_METADATA", line 410
ORA-06512: at "SYS.DBMS_METADATA", line 449
ORA-06512: at "SYS.DBMS_METADATA", line 1156
ORA-06512: at "SYS.DBMS_METADATA", line 1141
ORA-06512: at line 1
EXP-00056: ORACLE error 19206 encountered
EXP-00000: Export terminated unsuccessfully

Cause:
------

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

Fix:
----

Run the following script. that resides under
$ORACLE_HOME/rdbms/admin directory 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.

參考文獻：