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