Re: Survey: How many schemas is "many"
- From: Maaz Anjum <maazanjum@xxxxxxxxx>
- To: Seth Miller <sethmiller.sm@xxxxxxxxx>
- Date: Fri, 8 Aug 2014 13:52:15 -0400
What a great share. Thanks Seth and especially to Tim :)
On Fri, Aug 8, 2014 at 1:15 PM, Seth Miller <sethmiller.sm@xxxxxxxxx> 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@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
>>>
>>>
>>
>>
>
--
A life yet to be lived...
Other related posts:
