Re: Survey: How many schemas is "many"

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Thu, 7 Aug 2014 10:04:00 +0100
Message-ID: <CABe10sa9M4bARwmx3D2OGzdda8p677y-isedTWQp9gFoCFxiVw_at_mail.gmail.com>

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 Thu Aug 07 2014 - 11:04:00 CEST