Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Dropping public synonyms in a pluggable database

poundcake652Mar 4 2015 — edited Mar 9 2015

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?

This post has been answered by Salman Qureshi on Mar 6 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 6 2015
Added on Mar 4 2015
8 comments
4,135 views