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: how to group synonyms ?

Re: how to group synonyms ?

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: 28 Nov 2003 00:51:28 -0800
Message-ID: <1a75df45.0311280051.34d5bb3e@posting.google.com>


"mcstock" <mcstockspamplug_at_spamdamenquery.com> wrote

> thanks for the feedback, polite and otherwise...

Mark, my hackles are very quick to rise when it comes to Oracle recommendations that are wrong and not even debatable. I have apps designed to use public synonyms and these are causing me real problems, without a single friggen advantage that could not be done cleaner and better using local schema synonyms.

> i can see that there may be some valid points behind Billy's, shall we say,
> observation, but a little more articulation on specific problems would be
> helpful

In a nut shell.

Apps using & creating synonyms, also need to drop & re-create them (think about using synonyms like current_billing_month, prev_billing_month). Would you feel comfortable granting an app schema the priv to drop public synonyms?

Think SQL-Server and Oracle. SQL-Server database = Oracle Schema.

In that context, where & when would you use public synomyms? Looking at SQL-Server, something like a public synonym would be very powerful and sparingly used to resolve scope *across* databases.

Why then treat public synonyms differently in Oracle just because you do have this powerful feature?

Scope definition. You can have two similar schemas on the same database using the same table names. And btw, there is *no* valid reason why this should not be allowed. I have seen this numerous times in many Oracle instances though the years.

What now when it comes to public synonyms? Consider the danger where the schema that does need/use the public synonym looses its local synonym/table - and its app code still work as the scope of the object name is resolved at public synonym level.

Export an app schema. Import that app schema in another database. Where are the public synonyms? So much for a self-contained app schema.

> basically, i view public synonyms in the same light as OS, Java, and Apache
> path and virtual directory settings -- all in pretty common usage

Fine. But in Oracle a "logical database" should be a self-contained an independant schema. This precludes the use of public synonyms for app use IMO.

You mentioned Apache. Yep, not a problem. Unless you attempt to do it with a cluster of Apache servers... which is exactly what an Oracle instance is. A cluster of logical databases (aka schemas).

And btw, do not confuse an Oracle Schema with an Oracle User even though they physically are the same thing in Oracle.

> from a project development and testing standpoint, i've seen lots of
> problems with explicitly naming the schema owner --

Agree! Fully!! But that does NOT mean that you need *public* synonyms. Just synonyms.

> if it's
> not (totally) evil for oracle to create public synonyms for widely used
> objects, why object to the same practice for commonly used non-oracle
> objects?

Because Oracle's public synonyms deal with the instance and its contents.

You can achieve the same object owner transperancy with synonyms, with *better* control and management as this is localised - why then use public synonyms?

I consider the right to create and drop public synonyms a DBA role. Not for use by applications.

I however do use public synonyms. I create them for custom services & objects that are instance specific (think logon triggers calling stored procs, context procs, custom DBA tables and functions like sending mobile phone messages) .

The right tool for the job. Synonyms for app scope. Public synonyms for instance scope.

--
Billy
Received on Fri Nov 28 2003 - 02:51:28 CST

Original text of this message

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