hi there

"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.
This 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)
/

hope this might work

regards

vipul


-----Original Message-----
From: Mary Chibwana
[mailto:oracledba-ezmlmshield-x7591475.[Email address protected]
Sent: Tuesday, August 02, 2005 5:39 PM
To: LazyDBA Discussion
Subject: invalid synonym


Hi guys,

I am gettig the following error when I try to access some synonym:
ORA-00980- synonym translation is no longer valid.
How can I make it valid again.

Please help.

Maria


--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html




Oracle LazyDBA home page