| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: What do you think about multiples schemas versus 1 schema
Stephane,
Just thinking of a few potential issues (I've worked in a warehouse set up that way):
CONS
1) Duplicate objects (same name, different schema) - can be accidentally
created and cause a lot of confusion
2) Need to prefix objects with schema or create synonyms to get data from
one schema to the next
3) May hit a few errors regarding permissions across schemas - all can be
worked around though
PROS
1) Provides logical separation between objects
2) May make it easier to provide objects for adhoc reporting, etc (users
connected to dw cannot see stage tables)
3) In a complex environment it can help enforce good standards - like a dw
processing job should be able to read from stage but not update it (this
depends on the application design though)
I would recommend having a strong naming convention, even if using multiple schema's, so that the purpose of each table is understood without knowing which schema it is in - this helps fix con#1. I think the logical separation and ability to restrict access to end users are good reasons for choosing multiple schema's though.
I think schema's are a little like folders in a filesystem. Therefore your question is similar to "should I put all of my documents in a single folder or should I create folders for design, construction and testing. However I do understand the issue is slightly more complex than that.
Regards,
Mark.
paquette stephane
<stephane_paquette@ To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
yahoo.com> cc:
Sent by: Subject: What do you think about multiples schemas versus 1 schema
root_at_fatcity.com
01/11/2002 07:09
Please respond to
ORACLE-L
Hi,
What do you think about multiples schemas versus 1 schema, pros and cons.
For example, having the schemas: stage, dw, metadata or only dw.
I have my ideas but I want to check if do not missed anything.
TIA
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: stephane_paquette_at_yahoo.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).Received on Thu Oct 31 2002 - 15:28:53 CST
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Richard INET: mrichard_at_transurban.com.au Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).
 |
 |