Oracle-L: RE: Hundreds of schemas in one instance?

From: Ben Poels <poelsb_at_post.queensu.ca>
Date: Wed, 13 Mar 2002 08:03:41 -0800
Message-ID: <F001.00427448.20020313080341@fatcity.com>

The assumption is that every user of this system will have totally unique requirements. There will be no data sharing and no commonality of purpose. As far as alternatives I am not even thinking about multiple instances. Other alternatives include - many schemas and one tablespace, or many schemas and an arbitrary number of tablespaces, or an arbitrary number of schemas matched to an arbitrary number of tablespaces, or one schema for all users using fine grained access control (thanks for the idea Jack it sounds quite useful).

Peter: All users would be equal and no critical systems will be housed on this instance, but backup and recovery is definitely complicated by having so many data files and tablespaces. By the "opposite direction" do you mean only one tablespace.

Jared: The benefit is isolation. As much as possible keep the users from affecting each other. Performance effects are unavoidable but separate tablespaces would keep one user from gobbling up all the available disk space. Will definitely have to watch out for the data dictionary filling up the SYSTEM tablespace and bringing everything to a screeching halt.

Ben

-----Original Message-----
Sent: Wednesday, March 06, 2002 1:29 PM
To: Multiple recipients of list ORACLE-L

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: poelsb_at_post.queensu.ca

-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ben Poels INET: poelsb_at_post.queensu.ca 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: ListGuru_at_fatcity.com (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: DWILLIAMS_at_LIFETOUCH.COM 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: ListGuru_at_fatcity.com (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: Ben Poels INET: poelsb_at_post.queensu.ca 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: ListGuru_at_fatcity.com (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).

Content-Type: text/plain; name="ReadMe.txt"; charset="us-ascii" Content-Transfer-Encoding: 7bit The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact Postmaster_at_fatcity.com for clarification. ------_=_NextPart_001_01C1C75E.6E08AC80-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ben Poels INET: poelsb_at_post.queensu.ca 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: ListGuru_at_fatcity.com (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).

application/ms-tnef attachment: winmail.dat

Received on Wed Mar 13 2002 - 10:03:41 CST