Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Question: Database vs TableSpace (please help)
NNOOR wrote:
>
[snip]...
> We have two projects going, X and Y. Both are unrelated and need there
> own databases. Under MS SQL Server, we went into Enterprise Manager
> and simply created two DB's (as simple as right clicking and choosing
> a menu item). Then we only started the server and all of the DB's on
> it were accessible. On ORACLE, I think things work different way. Is
> TABLE SPACE equivalent to "databases" on SQL Server? In our example, are
> we better off creating two DATABSES on the Oracle server and startup
> both individually or create two TABLE SPACES, one for X and one for Y,
> under the same database (e.g. default ORCL database). If later is the
> case (i.e. using table spaces), does Oracle complain about having exact
> same table names under two different table spaces in the same database?
> How do we resolve conflicts? We looked at the documentation and and it
> seems that creating a database is quite long and complex process.
>
> WHAT IS THE BEST WAY TO GO???
With Oracle, your X and Y projects could be in 1 database (as different
schemas) or in 2 seperate databases on the same machine. Each setup has
it's advantages/disadvantages.
What is a database:
What is a tablespace:
What is a schema:
With all that in mind, which is better (1 DB with 2 schemas or 2 DB instances) depends on a number of things like: hardware, how much RAM and disk space you have to play with; what kind of performance you need (hardware); and your comfort level with the various administrative tasks.
The advantage of having 2 instances is that if one instance is having trouble and should grind to a halt, will NOT affect the other instance (depending on what the problem is). This is good for a Test and Production scenario where you don't have the luxury of another machine for a test DB alone. With 2 instances, you can tweak each instance to use different amounts of shared memory for the data and SQL cache.
Certainly having 1 DB instance with 2 schemas in it is easier to administer.
So what I would recommend is the following (your second proposition):
With a setup as above, you can later seperate PROJ_Y onto an instance of it's own, when you deem it right and when you learn how, quite seemlesly.
You should also look at creating a seperate rollback segment tablespace so your rollback segments are outside the SYSTEM tablespace. But doing this is kind of tricky.
Having said all that, you should read the Server Concepts Manual to get a much better appreciation for how Oracle does things. Knowing that, you can better decide what is best for your situation. Then read the Administrator's guide to figure out how to do things.
I hope this helps and Good Luck!
Zoran. Received on Sun Mar 23 1997 - 00:00:00 CST