Ben - Well, you don't give very many specifics, and only offer the
architecture that you favor, so sure, go for it. Since I seem to be the guy
that enjoys responding to vague questions, I'll provide some points for you
to consider.
I don't believe that Oracle will have a problem with hundreds of
schemas, per se. Oracle has very high limits for practically everything.
You don't spell out the alternative to this method, but in general I
would say that if the choice was between a single instance on a single
server and dozens of instances on that same server, I would go for a single
instance. With a single instance, you are letting Oracle manage the server
resources, such as memory. With multiple instances, the server O.S. has to
manage them, so to speak. Specifically, with a single instance, you can have
a larger SGA, larger db_block_buffers. Each schema can use the same buffer,
aging out blocks not recently used.
With multiple instances, the O.S. has to age portions of the
instance out to disk to accommodate the more recently active instances. We
have a test system with many instances. When you access an instance for the
first time, it seems that the O.S. first has to pull all the segments in off
disk. So there is a long delay for the first access, but subsequent accesses
are much faster.
The other question is whether there is data that some of these
schemas can share. Maybe there are common reference tables. Again a single
instance is simpler. Just have a common schema and give each schema read
access to that schema.
And as you point out, it is much easier to create a new schema than
to create a new instance plus a new schema.
Hope this answers the issue you are facing. If not, maybe you can
provide more details.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-----Original Message-----
Sent: Wednesday, March 06, 2002 1:29 PM
To: Multiple recipients of list ORACLE-L
Hi
Our university wants to set up a server that will provide groups
on campus with a standard set of services for web hosting, data
collection or whatever they want to do. As much as possible each
user should have their own isolated chunk of the server.
An Oracle database will sit in the background to provide whatever
database services they need. My thought is to go with one instance
with a unique schema, including separate tablespaces/datafiles, for each
user.
Some of the pros for this are:
- easy set up for new users
- easy software upgrades
- simplified tuning, backups, monitoring, auditing
- user isolation, especially disk space usage
- multiple instances would use far more memory
Some cons are:
- everyone must use the same release of the software
- database down time affects everyone
- might run into system maximums, for example max. number of datafiles
- an enormous SYSTEM tablespace
Has anyone had to this kind of thing? Any comments or suggestions?
TIA,
Ben
======================================================================
Ben Poels - Senior Technical Analyst - Queen's University at Kingston
Phone: 613.533.2449 Fax: 613.533.2168 Email: [EMAIL PROTECTED]
======================================================================
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ben Poels
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
