I have an Oracle 12c container database with several pluggable databases in it. In one of the pluggable databases, I de-installed OLAP by running the following scripts:
SQL> @%oracle_home%/olap/admin/catnoamd.sql
SQL> @%oracle_home%/olap/admin/olapidrp.plb
SQL> @%oracle_home%/olap/admin/catnoxoq.sql
SQL> @%oracle_home%/olap/admin/catnoaps.sql
After running the scripts, I then generated the SQL statements to remove invalid OLAP synonyms, by executing the following:
select 'drop public synonym ' || object_name || ';'
from dba_objects
where status <> 'VALID'
and owner = 'PUBLIC'
and object_type = 'SYNONYM'
and object_name like '%OLAP%';
When I execute the resulting SQL statements in the pluggable database, I get the following error for all of them:
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
If I switch back to the CDB$ROOT container and execute the same statements, I get the following error:
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
How can I remove these invalid public synonyms from my pluggable database?