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.
Our error screen shot:
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
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.
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
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%';