Oracle database with 100 schemas

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?

Thank you in advance,

daning Received on Tue Jun 11 2002 - 17:31:06 CDT