Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Re: Corporate Wide Public Synonyms ++ multiple schemas

From: Richard Foote <richard.foote_at_bigpond.nospam.com>
Date: Fri, 11 Feb 2005 13:42:40 GMT
Message-ID: <kD2Pd.156725$K7.65363@news-server.bigpond.net.au>


"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:oJidnYFUfNJ3NpHfRVn-rQ_at_comcast.com...
>
> "Richard Foote" <richard.foote_at_bigpond.nospam.com> wrote in message
> news:I%YOd.155917$K7.78680_at_news-server.bigpond.net.au...
>> <linuxgeek_00_at_yahoo.com> wrote in message
>> news:1108068838.968042.219230_at_f14g2000cwb.googlegroups.com...
>>> 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.

Cheers

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US