Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle database with 100 schemas

Re: Oracle database with 100 schemas

From: Roel Toledo <REMOVEroeltoledo_at_yahoo.com>
Date: Fri, 14 Jun 2002 02:30:20 +0800
Message-ID: <3d08e57c$1_8@news5.nntpserver.com>

"danny" <daningaddr_at_hotmail.com> wrote in message news:a619e2f9.0206111431.2c52f519_at_posting.google.com...
> 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

First I have a problem. I don't understand what you meant by "users playing the database."

If what you really mean is users will have blanket authority on their respective databases, then I would suggest (though not highly recommended) you create one instance for each user and have them "play around with it." Remember that the number of instances the Oracle software supports depends entirely on your hardware resources (memory, hard drive space, CPU speed) so be careful not to exceed your hardware's limit.

Much better idea is that you give each user one copy of Personal Oracle. In that way, you can let them play around with their own databases without slowing down the other users on the network.

Roel

REMOVE is not part of my email address. You know what to do if you need to send me an email. Received on Thu Jun 13 2002 - 13:30:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US