EXFSYS.DBMS_EXPFIL_DEPASEXP error duing expdp

  • A+
Categories:Oracle

Today our guy got a PLS-00201 error during the schema-level expdp task, and I searched the Oracle Support website but could not find extract answer, then found a similar webpage through Baidu.
http://wenku.baidu.com/link?url=U9HC44tu6RpjDbl8bu7hRZzM71QyHCvqneW4_eNmcN-rBUQNKbflReJy0Cm8F6hrlhA2_YZ6btHeo1k8Xgcm11jsxYqtkLAgTxEmESsjkpu
Our error screen shot:
EXFSYS.DBMS_EXPFIL_DEPASEXP error duing expdp

Then I found similar methods to resolve such issues in the Oracle Support website:
How To Install/Uninstall Expression Filter Feature or EXFSYS schema along with Rules Manager (文档 ID 258618.1)
Please note this section:

declare
  cursor cur1 is select synonym_name from all_synonyms where owner = 'PUBLIC' and table_owner = 'EXFSYS';
begin
  for c1 in cur1 loop
    EXECUTE IMMEDIATE 'drop public synonym "'||c1.synonym_name||'"';
  end loop;
end;
/

The following is a list of the public synonyms that may remain after just running CATNOEXF.SQL and these would be dropped by running one of the above scripts.

EXF$INDEXOPER
EXF$ATTRIBUTE
EXF$ATTRIBUTE_LIST
EXF$TABLE_ALIAS
EXF$XPATH_TAG
EXF$XPATH_TAGS
EVALUATE
DBMS_EXPFIL
USER_EXPFIL_ATTRIBUTE_SETS
ALL_EXPFIL_ATTRIBUTE_SETS
USER_EXPFIL_ATTRIBUTES
ALL_EXPFIL_ATTRIBUTES
USER_EXPFIL_DEF_INDEX_PARAMS
ALL_EXPFIL_DEF_INDEX_PARAMS
USER_EXPFIL_INDEX_PARAMS
ALL_EXPFIL_INDEX_PARAMS
USER_EXPFIL_ASET_FUNCTIONS
ALL_EXPFIL_ASET_FUNCTIONS
USER_EXPFIL_INDEXES
ALL_EXPFIL_INDEXES
USER_EXPFIL_PREDTAB_ATTRIBUTES
ALL_EXPFIL_PREDTAB_ATTRIBUTES
USER_EXPFIL_EXPRESSION_SETS
ALL_EXPFIL_EXPRESSION_SETS
USER_EXPFIL_PRIVILEGES
USER_EXPFIL_EXPRSET_STATS
ALL_EXPFIL_EXPRSET_STATS
RLM$EVENTIDS
RLM$TABLE_ALIAS
DBMS_RLMGR
USER_RLMGR_EVENT_STRUCTS
ALL_RLMGR_EVENT_STRUCTS
USER_RLMGR_RULE_CLASSES
ALL_RLMGR_RULE_CLASSES
USER_RLMGR_RULE_CLASS_STATUS
ALL_RLMGR_RULE_CLASS_STATUS
USER_RLMGR_PRIVILEGES
ADM_RLMGR_PRIVILEGES
USER_RLMGR_COMPRCLS_PROPERTIES
ALL_RLMGR_COMPRCLS_PROPERTIES
USER_RLM4J_EVENT_STRUCTS
USER_RLM4J_RULE_CLASSES

And this one:
Export Full Fails With Error EXP-00083 With Reference To XDB.DBMS_XDBUTIL_INT.system_info_exp After A Successful Deinstallation of XDB (文档 ID 1461971.1)
Please note this part:

However, some references to XDB were left behind in the database by the XDB deinstallaitio, in particular in the EXPPKGACT$ table.
The EXPPKGACT$ table is the datapump handler table that contains all the packages that should be executed to complete the export.

Another similar issue:
Expdp Fails With PLS-00201: Identifier 'Dmsys.Dbms_dm_model_exp' Must Be Declared (文档 ID 1243504.1)

From the above notes, I decided to do following steps to fix this issue:
1. Check if the user EXFSYS had been removed
2. Check if any synonyms of EXFSYS was left
3. Check if the table SYS.EXPPKGACT$ had some rows relating with the EXFSYS
4. Export the data again to check if the issue was resolved

SQL> select count(*) from dba_synonyms where table_owner like '%EXFSYS%';

  COUNT(*)
----------
        43

EXFSYS.DBMS_EXPFIL_DEPASEXP error duing expdp
EXFSYS.DBMS_EXPFIL_DEPASEXP error duing expdp
Done.

Sentences were used:

select 'drop public SYNONYM '||SYNONYM_NAME||';' from dba_synonyms where table_owner like '%EXFSYS%';
CREATE TABLE SYS.EXPPKGACT$_BACKUP AS SELECT * FROM SYS.EXPPKGACT$;
DELETE FROM SYS.EXPPKGACT$ WHERE SCHEMA like '%EXFSYS%';

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: