Re: Multiple Schemas vs. Multiple Instances

Since posting my original message, I discovered the undocumented feature of ALTER SESSION which allows default schema prefixing:

ALTER SESSION SET CURRENT_SCHEMA=<whatever>

This feature is unsupported, but supposedly in heavy use by imp/exp. Whether that makes me feel any better about banking on it, I'm not sure just yet.

In article <36AEF842.D118516C_at_capgemini.co.uk>,   Mike Burden <michael.burden_at_capgemini.co.uk> wrote:
> Yes, I too have thought about this problem.
>
> The approach is made more difficult because Oracle does not support a default
> schema prefix for a given user-id (does it??). So for instance if you create
two
> IDs.
>
> user1 default schema prefix travel
> user2 default schema prefix auto
>
> the same application could access different schemas just by changing the user
id's
> default schema prefix. I know the problem can be solved using synonyms but
these
> are just more objects to maintain.
>
> I bet this feature alone would remove the need for 95% of all synonyms and
hence
> less admin.
>
> Also trying to use two different schemas for the same set of tables means you
> can't use public synonyms. Each user-id therefore needs its own set of
synonyms
> which means you have synonyms coming out of your ears.
>
> The main problem I see though, is that you can't bring the database down
without
> stopping both applications. It's these types of issues (backup and recovery)
that
> usually sway the decision.
>
> garysadler_at_my-dejanews.com wrote:
>
> > We are faced with a crucial design decision and I'd like to benefit from
> > those Oracle DBA's out there who've more experience than I. I understand
that
> > you generally only need multiple instances in cases where significantly
> > different types of applications will be accessing the data. But we have a
> > situation where we'd like to "clone" application code in such a way that it
> > could access disparate sets of identically structured data depending upon
> > external settings.
> >
> > For example, take a table called Customer which contains the usual
> > customer-related stuff. We want to separate customers in the auto parts
> > industry from customers in the travel industry by placing them in separate
> > tables that are structured identically, and in fact both called Customer.
We
> > do this because we have a large code base that performs the same for auto
> > parts customers as it does for travel customers. Even though the apps may
be
> > running on different web servers, there's no reason to have different
> > application code. The web server must be able to tell whether it is running
a
> > travel app or an auto parts app and access the appropriate Customer table.
> >
> > Our dilemma is whether to use a design that involves separate instances for
> > auto parts and travel, or separate schemas and dynamically assigned
synonyms.
> > Anyone have input as to which is more scalable, flexible, and requires less
> > administration? Is there another approach that might be more appropriate?
> > Using all dynamic SQL is not an option. We'd rather not put an industry
> > identifier in the Customer table because the data model is quite complex and
> > we'd have to drag that industry id all over the place in order to keep the
> > customers separated. It seems to us that the multiple-instance solution
> > would require more administration, but would also be more scalable and
> > flexible. But then there would also be some administrative costs in
> > maintaining essentially duplicate schemas and the necessary synonyms. Your
> > advice is appreciated.
> >
> > Gary Sadler, DBA
> > Polygon Network Inc.
> > gary_at_polygon.net
> >
> > -----------== Posted via Deja News, The Discussion Network ==----------
> > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Jan 27 1999 - 12:28:31 CST