Skip to content
December 31, 2012 / doganay

DROP ORPHAN SYNONYMS


How to drop orphan (referenced object does not exist) synonyms (including public ones):

select decode(owner,'PUBLIC','drop public synonym "','drop synonym "'||owner||'"."')|| synonym_name ||'";' from dba_synonyms a
where not exists
(
select null from dba_objects b
where a.table_name=b.object_name
and a.table_owner=b.owner
);
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: