|
Tip of the Week
Tip for Week of November 30, 2003
Find Invalid Synonyms
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.
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)
/
|
Bookmark
