ORA-04063: package body "EXFSYS.DBMS_EXPFIL_EXP" has errors

今天同事遇到一個exp的問題，讓我幫忙解決，初步可以判定原因。

 

exp in  10.2.0.4

. exporting dimensions
. exporting post-schema procedural objects and actions
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "EXFSYS.DBMS_EXPFIL_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"EXFSYS.DBMS_EXPFIL_EXP"
ORA-06512: at "EXFSYS.DBMS_EXPFIL_DEPASEXP", line 61
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling
EXFSYS.DBMS_EXPFIL_DEPASEXP.schema_info_exp
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "DMSYS.DBMS_DM_IMP_INTERNAL" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"DMSYS.DBMS_DM_IMP_INTERNAL"
ORA-06512: at "DMSYS.DBMS_DM_MODEL_EXP", line 303
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling
DMSYS.DBMS_DM_MODEL_EXP.schema_info_exp
. exporting statistics
Export terminated successfully with warnings.

 

       上metalink 查詢錯誤ORA-04063 找到相關解釋，雖然很像，但是報錯的 package body 不是同一個，我在自己的測試環境模擬了一下錯誤。

       首先，把package body "EXFSYS.DBMS_EXPFIL_DEPASEXP" 進行edit, 然後compile出現錯誤，
select * from dba_objects w
where w.status <> 'VALID'
and w.owner = 'EXFSYS'

發現無效物件為：

DBMS_EXPFIL_EXP ，PACKAGE
DBMS_EXPFIL_EXP ，PACKAGE BODY

DBMS_EXPFIL_DEPASEXP ，PACKAGE BODY

DBMS_RLMGR_EXP ，PACKAGE BODY

此時，exp會出現上述錯誤，解決辦法為：

alter PACKAGE EXFSYS.DBMS_EXPFIL_EXP compile;
alter PACKAGE EXFSYS.DBMS_EXPFIL_EXP compile BODY;
alter PACKAGE EXFSYS.DBMS_EXPFIL_DEPASEXP compile BODY;
alter PACKAGE EXFSYS.DBMS_RLMGR_EXP compile BODY;

exp問題解決。但是這是在我的測試環境模擬出來的錯誤，在那個生產環境上invalid的物件有80多個，是EXFSYS下的，我覺得很奇怪，初步判斷可能是打patch或者當時安裝時導致的EXFSYS下的大部分物件無效，由於一些procedures是加密的所以暫時沒有檢視，懷疑是這個地方導致錯誤的產生。不過經核實，除了這個warning，基於schema的物件已經被正確匯出。

 

參考連線：

1.

2. metalink

EXP-00008 ORA-04063 ORA-06508 EXP-00083 DMSYS.DBMS_DM_UTIL [ID 457011.1]  

--------------------------------------------------------------------------------
 
  修改時間 04-SEP-2007     型別 PROBLEM     狀態 MODERATED  

In this Document
  Symptoms
  Cause
  Solution

 

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

 

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

Applies to:
Oracle Server - Enterprise Edition - Version: 10.1 to 10.2
This problem can occur on any platform.

Symptoms
The export jobs fails with the following errors:

EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "DMSYS.DBMS_DM_UTIL" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "DMSYS.DBMS_DM_MODEL_EXP", line 89
ORA-06512: at "DMSYS.DBMS_DM_MODEL_EXP", line 763
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling DMSYS.DBMS_DM_MODEL_EXP.schema_info_exp

Cause
Check for invalid objects in the database:

SQL> connect system/manager
SQL> set lines 200
SQL> select status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
from dba_objects where status != 'VALID' order by 4,2;

The output shows that package DMSYS.DBMS_DM_UTIL is invalid.

Package DMSYS.DBMS_DM_UTIL is used by the Oracle Data Mining option.

Solution
Please do the following:

SQL> CONNECT / as sysdba
Connected.

1) SQL>grant execute on utl_file to public;

2)Login as DMSYS user. If you are not using Oracle Data Mining then it would
fail during login because the account is locked.
Do the following to unlock the account and then login as DMSYS

SQL> ALTER USER dmsys IDENTIFIED BY dmsys ACCOUNT UNLOCK;

SQL> CONNECT dmsys/dmsys
Connected.

SQL> @$ORACLE_HOME/dm/admin/dmutil.plb
Package created.
Package created.

3)Now recompile the invalid dmsys packages if any,

SQL>alter package compile body;

After the package body is successfully recompiled try the export again.

 

3. Invalid Component After Upgrade From 10.2.0.2 To 10.2.0.3 [ID 471149.1]