Dropping or compiling a package fails with ORA-4043 On SYS_PLSQL_%
I have seen this issue crop up a few times at different sites and although there are some good technical explanations available online it can be a bit confusing because there is conflicting information. So the bug itself that I have experienced is when you try to remove a package or compile a package you get the error ORA-4043 object SYS_PLSQL_% does not exist.
The problem seems to arise from an automatically created DB object that is used for piped functions and has an object type of TYPE. If you query the object name you will see the same name is existing on all the schemas that have the same package.
From my experience, this issue seems to have appeared in the Oracle 12c DB although my research indicates it was previously a bug before but I didn’t come across it on 11g. I have found various MOS documents relating to this issue some will say there is no workaround but I am pleased to say there is a workaround. Further investigation in MOS and you can find several cases which have been superseded and end in a bug which claims to fix the issue but for some reason is only applicable if you have downgraded from 12.2 to 12.1. I have no idea why the patch would only apply to customers that have downgraded but I see this regularly on non-downgraded installs.
Bug 21068213 invalid TYPEs with names like "SYS_PLSQL_%" after downgrade from 184.108.40.206 to 220.127.116.11 - eg: SYS_PLSQL_GENERATE
The workaround I used can be found in Dropping a package fails with ORA-4043 on SYS_PLSQL_% (Doc ID 852695.1) and the bug 21068213 also mention you can drop them manually but doesn’t give the steps. Drop all synonyms which are returned by the query, no matter which schema.
You should always test this on a non-production environment and I would suggest asking Oracle for a patch so it doesn’t happen again. Should you have any additional questions around this topic, feel free to reach out to me directly on LinkedIn.