ORA-00980: synonym translation is no longer valid

This tip comes from Vikash Varma, Oracle DBA at Intelligent Consulting, in New Jersey.

"ORA-00980: synonym translation is no longer valid" is a common error encountered in a development environment. This can happen for many reasons.

Some of them are:

  1. You created a synonym on non-existing object by mistake.
    • For example, you created a synonym on SCOTT.DEPT where either the SCOTT schema in not present or the DEPT table is missing.
  2. You dropped an object but you did not drop the synonyms referencing the object.
  3. You dropped a user, but you did not drop synonyms referencing the objects owned by that user.

When an object is dropped, synonyms referring to the object are not dropped. The following script lists all such 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);

The following script generates DDL to drop synonyms whose translation is no longer valid. USE WITH CAUTION.

 
rem 
rem  Exludes SYS and SYSTEM users
rem
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)
/
 

Read more, a mini Tip on Synonyms

11 comments :

  1. You are awesome. Thank you.

    ReplyDelete
  2. This was very good help... thanks!

    ReplyDelete
  3. That was the right fix!
    Thank you sir!

    ReplyDelete
  4. thanks for the above query. that was very helpful, Abi

    ReplyDelete
  5. Thanks a lot

    This script is really helpful.


    Urmila

    ReplyDelete
  6. I didn't drop anything. This was totally unhelpful.

    ReplyDelete
  7. Nothing Happens again same error comes when I am grant all on em1(synonym name) to scott (user)

    ReplyDelete
  8. Nothing Happens again same error comes when I am grant all on em1(synonym name) to scott (user)

    ReplyDelete