ORA-00980: synonym translation is no longer valid

 

Error:  ORA-00980: synonym translation is no longer valid

Cause:

1. Synonym created on non-existing object by mistake.

2. The synonyms referencing the object is dropped but synonym is not dropped

3. Dropped a user, but the synonyms referencing the objects owned by that dropped user.

Query to find the invalid synonyms

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);

select 'drop '||decode (s.owner,'PUBLIC','PUBLIC SYNONYM ','SYNONYM'||s.owner||'.')||s.synonym_name||';' 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)

The query above generate the script to drop the synonyms whose translation is no longer valid