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:
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
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%';
