Re: Multiple databases

Mohammed,

A number of techniques are suggested below for wat you are trying to achive, the one you pick depends upon the business requirement and available resources.

1. One instance with multiple schemas. One will be core which will own all lookup tables & data. This schema will give appropiate privileges to each entitiey thrugh a role. Each entity will have its own schema (user who ownes the objects). This schema will in turn give privileges to role(s) for the end users. Public synonyms can not be used here as synonym resolution is not possible if synonym is the same with pointers to seprate schema objects. The workaround is to use schema.object_name in your code, or have synonyms not mirror the object_name, i.e co1.table_aa will have a public synonym called co1_table_aa. Each schema's ojects can be stored in a exclusive tablespace for security. Backups can be then done for the entire db, with exports for each schema for specific recovery issues.
2. Many instances, one for the core objects, and one each for each schema/company. This is very simple concept, the core data can be selected by using database links Security is not of the issue, recovery and backups then can be scheduled independely of other schemas. However in this case, resource usage (system memory) can be great. The concept here is that each instance will not be running at full capacity, all the time, but the minimumn resources will be required to have the instance(s) up all the time.
3. A instance for each schema/company, with core lookup objects being replicated to each instance. Here the issues with database links are resolved, however the issue with replaction is raised. This is very similar to #2, and most issues will be the same on these two approaches.

In either case your application code does not need to change, provided no hard coding of company info etc. is in the code. In case of data and reporting differences, application can handle this based upon parms for schema being processed. Use of input vars to the application will enable the operators to process each schema/company data.

My persommel preference is for option#1, in most cases, as that can reduce system resource usage, however the backup/recovery procedures have to be well planned.

Hope this helps.

--
Vikram Goel                                 Motorola email: vgoel_at_pts.mot.com
Sr. Oracle DBA - Consultant
Aerotek Inc.                                My email:  vgoel_at_emi.net

Motorola Info:
Mail Stop 39, Room S1014
1500 Gateway Blvd,
Boynton Beach, FL 33426 

In article <4l35qt$255_at_news1.mnsinc.com>, ghouse_at_neuraltech.com (Mohammed Ghouse) writes:


>

>We are in a planning stage to do the following:


>Which are different ways , one can have the following scenario.

>The processing invloves data from different companies ( A service buereuo type)

>The issues are 

>To seggregate the client data .

>To access the core look up tables from a central database/area.

>The other tables (VERY LARGE) , with the same names , 

>will be in different areas

>To use the same set of programs(without much customization )

>To give options to the operators to run the programs on the data 

>they want to .

>To efficiently manage the data (backup/recovery/scheduling the jobs)

>

>Any inputs will be appreciated.

>--Ghouse