Re: Maximum no of tables in an oracle database
Date: 14 Dec 2001 03:22:28 -0800
Message-ID: <1e562f83.0112140322.64827eb9_at_posting.google.com>
christianboivin1_at_hotmail.com (Chris) wrote in message news:<da20daf0.0112131610.76883e88_at_posting.google.com>...
> I don't think is a good idea to go with 90000 tables ....
>
> First solution
> i suppose your app can handle a user_id in all the table
>
> Second,
> I don't remember exactly but with Profile in Oracle,
> i think you can isolate your Data for each user
>
> if you decide to go with 90000 tables
> take a look at locally manage tablespace ...
> to give a chance to your data dictionnary !
>
> hth
>
> Chris
>
> databala_at_yahoo.com (Bala) wrote in message news:<dfe6bdb1.0112121116.20dff6ad_at_posting.google.com>...
> > For the OLTP app we are developing, we have the following scenarios,
> > 1. For each user created a separate schema is created(Set of tables
> > based on a template schema). We
> > have around 300 tables in the template schema. The maximum no of users
> > that the app would register would
> > be around 300. and that would amount to 90,000 tables. The figure
> > looks to be very high and never heard
> > of in case of any database. Can any one tell me what is the maximum
> > number of tables a oracle database
> > can support and any information about an existing database wherein
> > such huge number of tables are handled.
> > 2. The second scenario could be keeping the data related to all the
> > users in a single schema and use
> > relations between the tables. But the system handles proprietary data
> > and data should not be accessible
> > to any other user other than the one it is intended for.
> >
> > what would be the best solution?
> >
> > Thanks in advance
> > Bala
Hi,
Is there some upper limit on the number of tables that can exist per
database instance? Also, would the presence of such a huge number of
tables hamper performance in any respect?
In case you don't want to have 90000 tables, you could probably try
using partitions per user using user_id as the range yardstick. Thus
you would have a master schema that houses all the tables and for each
table you would have 300 partitions. I'm not sure how you could handle
the security aspect though.
An Advantage of this approach would be that way you wouldn't face
performance bottlenecks later if the data grows too large for each of
the users.
I'm not sure if there is an upper limit on the number of partitions
that are allowed.
Also, if a user is to be dropped or something, it would be easier to
maintain data.
Trust this helps.
Regards,
Ram.
Received on Fri Dec 14 2001 - 12:22:28 CET