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:
cursor cur1 is select synonym_name from all_synonyms where owner = 'PUBLIC' and table_owner = 'EXFSYS';
for c1 in cur1 loop
EXECUTE IMMEDIATE 'drop public synonym "'||c1.synonym_name||'"';
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.
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%';
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%';