RE: Survey: How many schemas is "many"
- From: "Chitale, Hemant K" <Hemant-K.Chitale@xxxxxx>
- To: <niall.litchfield@xxxxxxxxx>
- Date: Thu, 7 Aug 2014 17:18:00 +0800
I would think more than 10 (yes, 10 not 100) schemas in a database would be a
multi-application database ! (Except for products like EBusiness Suite that
has many schemas but uses only 1 account to execute DML).
A large number of schemas in a database is the result of a consolidation
exercise. So there seem to have been very many consolidations with a single
database supporting multiple applications (services).
Hemant K Chitale
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Niall Litchfield
Sent: Thursday, August 07, 2014 5:04 PM
To: William Robertson
Cc: ORACLE-L
Subject: Re: Survey: How many schemas is "many"
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
This email and any attachments are confidential and may also be privileged. If
you are not the intended recipient, please delete all copies and notify the
sender immediately. You may wish to refer to the incorporation details of
Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at
https://www.sc.com/en/incorporation-details.html.
Other related posts:
