Re: Survey: How many schemas is "many"
Date: Thu, 07 Aug 2014 08:57:01 -0600
Message-ID: <53E393BD.5050503_at_evdbt.com>
Back in 1996 while I was working for Oracle Consulting, contracting for the consolidated DW at the telecom formerly known as USWest (then Qwest, now CenturyLink), I received a strange email from a guy on another project in the company...
/When you delete a user from an Oracle database, does it automatically reclaim the space in the data dictionary?/
Too vague to answer right away, and after some back-n-forth got down to the root, which was that he wanted to know if the space in the SYS.USER$ would be "freed" for "reclaim" by a future CREATE USER command. OK, easy enough: Oracle7, PCTUSED, PCTFREE, FREELISTS, yadda, yadda and it seemed his question was answered.
But the weirdness and specificity of the question nagged, so I asked around about the guy. Didn't have to dig far...
It turned out that he was the "architect" on a custom-built internal application called "OSPFM" (I think it meant "outside plant and facilities management"), and they had designed it so that each Oracle user/account had it's own separate schema.
There were *thousands* of Oracle user/accounts. Each with its own complete and independent schema. Thousands of schemas.
I kid you not.
Not going to get into any of the nonsense reasoning that resulted in this, but the project actually succeeded (sort of) and went "live". Of course, it didn't outlast the century, and eventually someone brought order to chaos and redesigned it conventionally; the advent of partitioning helped. 3rd-hand rumors have told me that they had more than 20,000 user accounts and schemas before this happened.
Anyway, long answer to short question, but as far back as Oracle7, there are no limits.
Unfortunately.
On 8/7/14, 8:11, Jeremy Schneider wrote:
> I've done quite a bit of work on databases with several hundred
> schemas - including some over 250. As Hemant pointed out, these are
> highly consolidated databases; each schema is a a different
> application. It actually worked very well on the systems I worked on,
> after we solved a few unique challenges. Found the limits of resource
> manager and some tuning tools, but came up with good creative
> solutions to do resource management and tuning on dbs with a huge
> number of applications.
>
> -J
>
> --
> http://about.me/jeremy_schneider
>
>
> On Thu, Aug 7, 2014 at 6:04 AM, Karth Panchan <keyantech_at_gmail.com
> <mailto:keyantech_at_gmail.com>> wrote:
>
> 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 <mailto: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
>> <mailto: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
>> <mailto: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-lReceived on Thu Aug 07 2014 - 16:57:01 CEST