Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from puck1183.startdedicated.com (localhost [127.0.0.1]) by puck1183.startdedicated.com (Postfix) with ESMTP id C71CF19604C0 for ; Fri, 8 Aug 2014 19:17:28 +0200 (CEST) Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by puck1183.startdedicated.com (Postfix) with ESMTP for ; Fri, 8 Aug 2014 19:17:28 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 644112C831; Fri, 8 Aug 2014 13:17:27 -0400 (EDT) Authentication-Results: turing.freelists.org; dkim=fail (verification failed; insecure key) header.i=@gmail.com; dkim-adsp=none (insecure policy) X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id C5-1Blqn84HC; Fri, 8 Aug 2014 13:17:27 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9D9D42C806; Fri, 8 Aug 2014 13:16:46 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 08 Aug 2014 13:16:05 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 601F72C7D5 for ; Fri, 8 Aug 2014 13:16:05 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id cgaALahl6lAZ for ; Fri, 8 Aug 2014 13:16:05 -0400 (EDT) Received: from mail-qg0-f54.google.com (mail-qg0-f54.google.com [209.85.192.54]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 204E92C7CD for ; Fri, 8 Aug 2014 13:15:36 -0400 (EDT) Received: by mail-qg0-f54.google.com with SMTP id z60so6493406qgd.27 for ; Fri, 08 Aug 2014 10:15:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=OoR5hCWkP8ztU8ZJsVO63wI4+U9rUI3KoNiZOF1zBx4=; b=oYUy+Or/Gffi0jMVQ4peoYH0i0UTeFDo5BO3hXNBre8JzZ6mFjoN/b2llJo1bWcww0 7I8024Uerr0DtRjXxcHS++CMOUc3yiD97hG2nYBg6onaDS1ZRvLckGTrp3C3DJcyTwUY SGvYF3FmXCNs+Q2QaxKkLg7/Ebnz91N7TPbE85ym7agasw4lFSXYEXbcB+wn13REMd0b 35KIUim4bzUk1ThZM0UWbOsbLSAdei+YsXLRyr8KJfShtVxk6Kjusb13FzjKd8BAfRB1 iOp4nwhCXXGq499v5s6iWWPPaM5t/7HYOziLXSqj5ikQ6UjpwTXBjU1oqh0HZcE5A6UG udrA== MIME-Version: 1.0 X-Received: by 10.224.128.9 with SMTP id i9mr40646031qas.50.1407518134637; Fri, 08 Aug 2014 10:15:34 -0700 (PDT) Received: by 10.140.51.2 with HTTP; Fri, 8 Aug 2014 10:15:34 -0700 (PDT) In-Reply-To: <53E393BD.5050503@evdbt.com> References: <5241754498648019715@unknownmsgid> <170E6053-48E1-4A89-BF10-8971149706C4@gmail.com> <53E393BD.5050503@evdbt.com> Date: Fri, 8 Aug 2014 12:15:34 -0500 Message-ID: Subject: Re: Survey: How many schemas is "many" From: Seth Miller To: Tim Gorman Cc: Oracle-L Freelists Content-Type: multipart/alternative; boundary=001a11c2cafc6e26700500215ad6 X-archive-position: 55863 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: sethmiller.sm@gmail.com Precedence: normal Reply-To: sethmiller.sm@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --001a11c2cafc6e26700500215ad6 Content-Type: text/plain; charset=UTF-8 Tim, This sounds like the extreme opposite of the "Vision " application. Seth Miller On Thu, Aug 7, 2014 at 9:57 AM, Tim Gorman 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 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 >> 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 >> >> 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 :) >> >> >> >> >> On Thu, Aug 7, 2014 at 8:58 AM, William Robertson < >> william@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 >>> 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 >> >> > > --001a11c2cafc6e26700500215ad6 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Tim,

This sounds like the extreme oppos= ite of the "Vision" application.

Seth= Miller


On Thu,= Aug 7, 2014 at 9:57 AM, Tim Gorman <tim@evdbt.com> wrote:
=20 =20 =20
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 futur= e CREATE USER command.=C2=A0 OK, easy enough:=C2=A0 Oracle7, PCTUSED, PCTFREE, FREELI= STS, 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.=C2=A0 Didn't have to dig far...

It turned out that he was the "architect" on a custom-built i= nternal application called "OSPFM" (I think it meant "outside pl= ant 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.=C2=A0 Each with its own complete and independent schema.=C2=A0 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&= quot;.=C2=A0 Of course, it didn't outlast the century, and eventually someone brought order to chaos and redesigned it conventionally; the advent of partitioning helped.=C2=A0 3rd-hand rumors have told me that they ha= d 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. =C2=A0As Hemant pointed out, these are highly consolidated databases; each schema is a a different application. =C2=A0It actually worked very well on the systems I worked on, after we solved a few unique challenges. =C2=A0Found 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



On Thu, Aug 7, 2014 at 6:04 AM, Karth Panchan <keyantech@gmail.com> wrote:
I understand more schema's is difficult to maintain.= =C2=A0

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 SQLLI= B error from Oracle.=C2=A0

Anyone worked/faced issues with around 250 schema's = ?

BTW our new application modified to handle in single schema.=C2=A0

Karth

Sent from my IPhone=C2=A0

On Aug 7, 2014, at 5:04 AM, Niall Litchfield <niall.litchfield@= gmail.com> wrote:

That's a fair question, Patrice&= #39;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 hav= e 60 responses, the results so far (DB account =3D any user, schema =3D 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 threa= d the DBDiff feature of SQL*Developer isn't really intended to be used at that sort of scale. =C2=A0

Total DB accounts

0-10=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 15.00%
10-100=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = 40.00%
100-500=C2=A0=C2=A0=C2=A0=C2=A0 28.33%
500-1000=C2=A0=C2=A0 10.00%
1000-5000=C2=A0=C2=A0 1.67%
5000+=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 5.00%


Total Schemas

0-10=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 31.67%
10-100=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = 45.00%
100-500=C2=A0=C2=A0=C2=A0=C2=A0 18.33%
500-1000=C2=A0=C2=A0=C2=A0=C2=A0 3.33%
1000+ =C2=A0 =C2=A0 =C2=A0 =C2=A0 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@williamr= obertson.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=C2=A0


On 5 Aug 2014, at 14:35, Niall Litchfield <niall.litchfield@g= mail.com> wrote:

All

For those not following the dbdiff thread I've created a 2 question survey at=C2= =A0htt= ps://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.=C2=A0

--
Niall Litchfield
Oracle DBA
http://www.orawin.info



--
Niall Litchfield
Oracle DBA
http://www.orawin.info



--001a11c2cafc6e26700500215ad6-- -- http://www.freelists.org/webpage/oracle-l