Re: referencing objects

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:954353669.2363.1.pluto.d4ee154e_at_news.demon.nl...
>
> Sorry,
> strongly disagree about that. It is extreemly BAD practice to do that.
> You are loosing transparency. Whenever you want to maintain a development
> and production scheme, you can easily do that by using private and public
> synonyms.
> When you hardcode the schema owner, you'r stuck forever, and you end up
> setting up a second istance, which makes things only more difficult.
>

And use of public synonyms is a performance dog, as I'm sure you'll be aware.

For a statement that refers to a single table only, the CPU usage during the parse phase is approximately 1:2:4 for a fully-qualified reference, a private synonym and a public synonym respectively. For complex SQL statements, the cost of synonym usage is even greater. The synonym based solutions involve potentially large library cache dependency tables, and are sensitive to the aging out of the library cache objects representing the table name in the users' own schemas. These library cache objects cannot be "kept". In an instance with high parse rates, the use of synonyms increases CPU usage dramatically, and often causes contention on the latches that control access to the library cache, dictionary cache and shared pool.

It's not recommended.

Regards
HJR
> Regards,
>
> Sybrand Bakker, Oracle DBA
>
>
>
> Rob Calfee <trace_at_primenet.com> wrote in message
> news:8btd7o$9jq$1_at_nnrp02.primenet.com...
> > Louis,
> >
> > I'll be honest. It is always a good coding policy to use the schema
prefix
> > in all PL/SQL development because of the problem you just discussed.
This
> > should come natural to any PL/SQL developer because the policy only
makes
> > sense because it identifies the location of objects which helps future
> > coders (every company has that revolving door of employees) pick up
where
> > the last coder left off. You should start the policy now of coding in
the
> > schema prefix. This will only help in the future. Then you can give
> rights
> > on internet to inet and you will be finished.
> >
> > Rob Calfee
> > Systems Consultant
> > Quest Software
> > 602-741-9419
> > 520-421-2734/7124
> > rcalfee_at_quest.com
> > www.quest.com
> >
> >
> >
> > Louis <frolio_at_videoshare.com> wrote in message
> > news:8bt8kk$k5f$1_at_bob.news.rcn.net...
> > > Greetings All, I have created an Oracle 8i database that is to
> > > be used on a web database server. I have created a schema
> > > called "internet" and have been creating objects(tables, procedures,
> > > etc.) in this schema. Now, I have created a user called "inet" which
> > > will be the default user that will connect to the database via the web
> > > server.
> > > My question is this: When the web team designs their web pages and
> > > they go to reference a procedure in the "internet" schema do they need
> > > to preface the procedure call with the schema name? For example:
> > > internet.spProcedure. Is it possible to have the login user "inet"
call
> > > the procedure without prefacing it with the schema name? For example:
> > > spProcedure. I ask this because we are coming off a SQL server
database
> > > and all the object where compile under the dbo user and in this case
> when
> > > a procedure call takes place from the web server the schema name is
not
> > > needed. It is my hope to mimic this in Oracle so that our web team
does
> > not
> > > have to go through all their code to add the schema name to each
> procedure
> > > call.
> > >
> > > Any help would be greatly appreciated.
> > >
> > > Yours, Louis
> > >
> > > frolio_at_videoshare.com
> > >
> > >
> >
> >
>
>
Received on Fri Mar 31 2000 - 08:39:20 CST