ORA-00980: synonym translation is no longer valid

This error occurs when you have a public synonym defined and the object referencing or owning the synonym has been dropped or deleted. You can check for the invalid synonyms using below query.

select * from dba_synonyms s

where table_owner not in(‘SYSTEM’,’SYS’)

and db_link is null

and not exists

(select 1

from dba_objects o

where s.table_owner=o.owner

and s.table_name=o.object_name);

 

Solution:

Replace the synonym with the name of the object it references or re- create the synonym so that it refers to a valid table, view, or synonym.

The Invalid synonyms must be dropped and recreated with the right owner. I normally do this using below query.(In this example the owner of the synonym was dropped and I am altering the synonym to reference the new owner)

DROP PUBLIC SYNONYM SYNONYM_NAME;
CREATE PUBLIC SYNONYM SYNONYM_NAME FOR NEW_OWNER.OBJECT_NAME;

Leave a Reply