Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Corporate Wide Public Synonyms ++ multiple schemas

Re: Corporate Wide Public Synonyms ++ multiple schemas

From: Richard Foote <>
Date: Fri, 11 Feb 2005 13:42:40 GMT
Message-ID: <kD2Pd.156725$>

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message
> "Richard Foote" <> wrote in message
> news:I%YOd.155917$
>> <> wrote in message
>>> My company wants to implement a corportate wide public synonym model
>>> for our oracle 9i database. Meaning that every table in every schema
>>> would have a public synonym created against it. Personally I think
>>> this is a bad idea. However I need to develop a convincing arguement
>>> to change this policy that has been suggested by our "DBA's".
>>> One issue is that I'm aware of is that all table names must be unique
>>> across all schemas. Please let me know other reasons why it is a bad
>>> idea to create public synonyms for every table in every schema.
>> At a site I work, they previously had a couple of thousand or so users
>> accessing everything through public synonyms. Latch contention was a
>> significant performance bottleneck.
>> By using setting the appropriate current_schema via a logon trigger, the
>> associated latch contention issues pretty well vanished.
>> Avoid public synonyms if practical.
>> Cheers
>> Richard
> been following bits and pieces of this thread, and checking some
> references regarding the seemingly geometric growth of latches when
> over-using public synonyms
> a database trigger to alter the schema ia all well and good -- but that
> assumes all application objects are in the same schema doesn't it? or is
> the rest of the strategy to have private synonyms in the 'main' schema
> that would than allow schema-independent references to other objects (or
> was this covered in part of the thread that i missed?)

Hi Mark,

Yes, in my specific example, there was just the one production schema.

However, it doesn't necessarily have to be the case. If there's something about the connecting session that might identify what application/schema within the database might be appropriate (such as the program, module, etc.), then a similar course of action could be adopted with a piece of case logic in the logon trigger.


Richard Received on Fri Feb 11 2005 - 07:42:40 CST

Original text of this message