Re: Survey: How many schemas is "many"
- From: Alfredo Abate <alfredo.abate@xxxxxxxxx>
- To: tim@xxxxxxxxx
- Date: Thu, 7 Aug 2014 11:28:41 -0500
"3rd-hand rumors have told me that they had more than 20,000 user accounts"
That's insane!
The most I have had is 250+ schemas due to Oracle EBS.
Alfredo
On Thu, Aug 7, 2014 at 9:57 AM, Tim Gorman <tim@xxxxxxxxx> wrote:
> 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@xxxxxxxxx> 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@xxxxxxxxx>
>> 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@xxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxx>
>>> 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
>>
>>
>
>
Other related posts:
