Re: Survey: How many schemas is "many"

From: Karth Panchan <keyantech_at_gmail.com>
Date: Thu, 7 Aug 2014 07:04:52 -0400
Message-Id: <170E6053-48E1-4A89-BF10-8971149706C4_at_gmail.com>

I understand more schema's is difficult to maintain.

Are there any limitation on number of schema's in Oracle 11g RAC?

Supporting old application with 250 schema's per DB. I was told more than 250 schema's will cause some SQLLIB error from Oracle.

Anyone worked/faced issues with around 250 schema's ?

BTW our new application modified to handle in single schema.

Karth

Sent from my IPhone

> On Aug 7, 2014, at 5:04 AM, Niall Litchfield <niall.litchfield_at_gmail.com> wrote:
>
> That's a fair question, Patrice's original question arose in the context of the SQL Developer diff tool for comparing schemas in 2 different databases. In that context I considered an empty schema to count towards the number of schemas in a db since you definitely want to know if it is empty in db A but populated in db B. However it did seem likely to me that most people would go with your definition - hence Q2.
>
> As I'm away for a bit now, and we have 60 responses, the results so far (DB account = any user, schema = user owning objects) are below. So the anecdotal evidence from this list is that it is unusual, but hardly unheard of, to have > 100 users owning database objects. If anyone missed Jeff's later reply on the other thread the DBDiff feature of SQL*Developer isn't really intended to be used at that sort of scale.
>
> Total DB accounts
>
> 0-10 15.00%
> 10-100 40.00%
> 100-500 28.33%
> 500-1000 10.00%
> 1000-5000 1.67%
> 5000+ 5.00%
>
>
> Total Schemas
>
> 0-10 31.67%
> 10-100 45.00%
> 100-500 18.33%
> 500-1000 3.33%
> 1000+ 1.67%
>
> Niall
> <pedantry>
> I'd go with schema as being a set of objects in a single namespace and of course would say that that must logically include the empty set :)
> </pedantry>
>
>
>

>> On Thu, Aug 7, 2014 at 8:58 AM, William Robertson <william_at_williamrobertson.net> wrote:
>> How are we defining "schema"? To me it's a collection of database objects owned by a single account (or equivalent namespace), so I was a bit puzzled by the two-part question. A user that owns no objects (such as a read-only production account) is not a schema, surely.
>> 
>> William Robertson 
>> 
>> 
>> On 5 Aug 2014, at 14:35, Niall Litchfield <niall.litchfield_at_gmail.com> wrote:
>> 
>> All
>> 
>> For those not following the dbdiff thread I've created a 2 question survey at https://www.surveymonkey.com/s/VGKZMY5 to get some statistics on how many different schemas databases in the wild actually contain. If we get more than, say, 50 responses I'll post back the answers here. 
>> 
>> -- 
>> Niall Litchfield
>> Oracle DBA
>> http://www.orawin.info

>
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Aug 07 2014 - 13:04:52 CEST