Re: Survey: How many schemas is "many"
Date: Fri, 08 Aug 2014 14:53:10 -0600
Message-ID: <53E538B6.2070401_at_evdbt.com>
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
> <mailto: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 <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 Fri Aug 08 2014 - 22:53:10 CEST