Re: Oracle database with 100 schemas

Either way, you play database is going to have to have a very large shared pool to deal with all the rowcache information child cursors. Parsing could be very slow.

An alternative - which may be viable -

    one table = one view.

Add a column data_owner to each table,
and a before_insert trigger that populates the column with the current user name.

    Create viewX as
    select * from tableX
    where data_owner = user;

Create a histogram of 200+ columns on
the data_owner column on each table unless every user has about the same amount of data in every column.

Include the data_owner in every index as the first column.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            June / July
        Australia      July / August
http://www.jlcomp.demon.co.uk/seminar.html

danny wrote in message ...


>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