Re: Multiple Schemas vs. Multiple Instances

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
Received on Wed Jan 27 1999 - 05:28:03 CST