Re: Survey: How many schemas is "many"

From: Jeremy Schneider <jeremy.schneider_at_ardentperf.com>
Date: Thu, 7 Aug 2014 09:11:58 -0500
Message-ID: <CA+fnDAZ70CTEyhjP6-Nx9ArbdMsn4HTMo8D-FEHJvBL==FNFjQ_at_mail.gmail.com>

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_at_gmail.com> 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_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-l

Received on Thu Aug 07 2014 - 16:11:58 CEST