Re: Survey: How many schemas is "many"

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Fri, 8 Aug 2014 17:50:51 -0700
Message-ID: <CAA2Dszx+WQsQ7A+YviR0Q=oDVFDKRcPVN0gQ5X=1QuRW=W0XzA_at_mail.gmail.com>



TIm,

   I had a good laugh :) Randy took "simple design" to an extreme level.

I consulted for a dot com company a while ago: You can create a community (and simple communities were free), and each community maps to a schema in the database. If my memory serves right, there were about 30,000 schemas in that database. Of course, only about 1000 communities are active though, just about 100 of them are very active. However, all 30K schemas have to be available in the database :(

There were numerous challenges in the shared pool and dictionary size area.

Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com - Specialists in Performance, RAC and EBS
Blog: http://orainternals.wordpress.com/ Oracle ACE Director and OakTable member <http://www.oaktable.com/>

Co-author of the books: Expert Oracle Practices <http://tinyurl.com/book-expert-oracle-practices/>, Pro Oracle SQL, <http://tinyurl.com/ahpvms8> <http://tinyurl.com/ahpvms8>Expert RAC Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL practices <http://tinyurl.com/book-expert-plsql-practices>

<http://tinyurl.com/book-expert-plsql-practices>

On Fri, Aug 8, 2014 at 1:53 PM, Tim Gorman <tim_at_evdbt.com> wrote:

> Thanks for pointing that out, I should have remembered that...
>
> Wheeeeeeeeeee! Love this job!
>
>
>
>
>
> On 8/8/14, 11:15, Seth Miller wrote:
>
> Tim,
>
> This sounds like the extreme opposite of the "Vision
> <https://www.simple-talk.com/opinion/opinion-pieces/bad-carma/>"
> application.
>
> Seth Miller
>
>
> On Thu, Aug 7, 2014 at 9:57 AM, Tim Gorman <tim_at_evdbt.com> 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_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>
>>> 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 Sat Aug 09 2014 - 02:50:51 CEST

Original text of this message