Re: creating public synonyms for a set of tables (newbie)

I might add, especially if other applications are in separate schemas in the same instance, you might wish to check for the existence of any other Public Synonyms matching the names of the synonyms you are about to create, prior to creating them. This will avoid the unpleasant possibility of altering an existing public synonym for another application and potentially crashing that app.

(In our case, two apps from different areas were placed
in one instance, both with tables named DEPARTMENT. The DBA creating Public Synonyms did not check first and destroyed the synonym to the first app with the synonym to the second app, causing the first app to explode...)

Something like this might be a decent test:

Select synonym_name, table_owner
from All_synonyms
where owner= 'PUBLIC'
and synonym_Name in
(select table_name

 from user_tables)

(this could be expanded to check for view names,
functions and procedures, sequences, etc.... but you get the idea...)

Good Luck

Robert Proffitt
Beckman Coulter
RTProffitt_at_beckman.com

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Apr 17 2000 - 00:00:00 CDT