Tim,
This sounds like the extreme opposite of the "Vision
<https://www.simple-talk.com/opinion/opinion-pieces/bad-carma/>"
application.
Seth Miller
On Thu, Aug 7, 2014 at 9:57 AM, Tim Gorman <tim@xxxxxxxxx
<mailto:tim@xxxxxxxxx>> 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
<keyantech@xxxxxxxxx <mailto:keyantech@xxxxxxxxx>> 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@xxxxxxxxx
<mailto:niall.litchfield@xxxxxxxxx>> 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@xxxxxxxxxxxxxxxxxxxx
<mailto: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
<mailto: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
