Skip to content

How to Resolve ORA-01434: private synonym to be dropped does not exist

  • by

ORA-01434

Tried to drop a private synonym, but it failed with ORA-01434.

SQL> show user
USER is "OE"
SQL> drop synonym country;
drop synonym country
             *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist

ORA-01434 means that the private synonym you specified in the statement is not found, so it cannot be drop in this moment. Most likely, you misspelled the name, or you should qualify the synonym by its schema name.

Solutions

First of all, we should check synonym names.

SQL> column synonym_name format a16;
SQL> select synonym_name from all_synonyms where owner = 'OE' order by 1;

SYNONYM_NAME
----------------
COUNTRIES
...
6 rows selected.

Then we correct the name in the statement.

SQL> drop synonym countries;

Synonym dropped.

Sometimes, the user may not be the owner of private synonym, therefore, we should fully qualify the synonym.

SQL> show user
USER is "SYSTEM"
SQL> drop synonym oe.countries;

Synonym dropped.

As you can see, we added the schema name to fully qualify the object.

Leave a Reply

Your email address will not be published. Required fields are marked *