Re: Survey: How many schemas is "many"
Date: Thu, 7 Aug 2014 10:50:51 +0100
Message-ID: <-7434671511188445543_at_unknownmsgid>
Good point about the empty set, and I'm all for pedantry :)
In practice (and from a security audit point of view) there is a big difference between a read-only account with no CREATE privileges or direct grants and a designed application schema. At my last job we had a situation where a private synonym would have solved a problem for an application we couldn't easily change, but we couldn't do it as that would have reclassified the app connection account as a schema, requiring a whole new request submission to avoid a policy violation (and the request was rightly turned down because applications should not connect directly to schemas). At my current site we have a handy support account on production that can create its own tables, but you wouldn't want to diff it with anything as it's full of CTAS junk, and in any case it's about to be removed for security reasons. So I'd say there is a useful difference between "account" and "schema", and if people really mean "schemas" I'm surprised anyone has more than about 10 excluding Oracle system ones. I'm not sure which of us is being more pedantic, though.
William
On 7 Aug 2014, at 10:04, 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-lReceived on Thu Aug 07 2014 - 11:50:51 CEST