Re: Oracle database with 100 schemas

In article <a619e2f9.0206111431.2c52f519_at_posting.google.com>,  daningaddr_at_hotmail.com (danny) wrote:

> Hi All,
>
> I am facing a design issue. I have a project which let users can
> connect database to play in what-if mode, they can try anything they
> want without interfere others, as if each user have their own private
> database (The concept is exactly like Oracle 9i Virtual Private
> database. but we don't want to spend money for that option).
>
> We are considering two solutions. Suppose there are 100 users that can
> play with database in the same time,
>
> 1. Create 100 schemas. Data are separated in schemas.
> 2. Create 1 schema, but create 100 views for each table. Different
> connecting user will query different views. Each base table has a view
> id to indicate that certain row will belong to which view. This
> approach will separate data vertically. Current we have around 200
> tables in one schema, so that comes out we will have 200*100 views in
> that schema.
>
> Since all the objects information will go to the dictionary in the
> system tablespace. The system tables will grow very big. I am worry
> about MANAGEABILITY and PERFORMANCE about this database. I'd like to
> hear you guys' opinion about the above approach. Anybody have
> experience to a database has hundred schemas?

Which one appeals to you most? Why?

Will your users have INSERT and UPDATE privileges? Can they CREATE, DROP or TRUNCATE? This might have some bearing on your answer.

-- 
The underscore character does not belong in my address. You know the drill...
***
Anyone sufficiently smart enough to configure and use USEnet for research should
be smart enough to Read The Freakin' Documentation!