Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Question: Database vs TableSpace (please help)

Re: Question: Database vs TableSpace (please help)

From: Zoran <zoranm_at_echo-on.net>
Date: 1997/03/23
Message-ID: <33355F9B.2D38@echo-on.net>

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:



A database in Oracle terms, is also reffered to an INSTANCE. An instance is a complete set of processes (including shared memory address space for the DB), control files, redo-log files, a SYSTEM tablespace and perhaps (probably) other tablespaces (for data, indices and rollback segments). Each DB instance is identified by ORACLE_SID. When you install Oracle Workgroup Server, and it sets up a default instance, I believe the SID is ORCL.

What is a tablespace:



A tablespace in Oracle is a hunk of diskspace that belongs and is managed by one and only one database instance. When a DB instance is created (with CREATE DATABASE SQL command) a SYSTEM tablespace is mandatory. A tablespace is tied to a specific file in your NTFS filesystem. In a tablespace you can put data tables, indices or roll-back segments. If desired, you could have data tables, indices and roll-back segments all in one tablespace; NOT RECOMMENDED.

What is a schema:



A schema is an Oracle USER that owns objects like tables, indices and stored procedures, synonyms (but not a tablespace). A table is uniquely identified (within a DB instance) as schema.table_name. Thus, you *can* have the same table names under 2 different schemas - it just depends on which schema (or userid) you use to create the tables with. So, it is obvious, that within one schema, you cannot have 2 tables with the same name. Different schemas can share objects with any other schema with the use of ROLES and PRIVELEGES. Privileges come in 2 flavours: system and object. SYSTEM privileges dictate who can do what and object privileges dictate who can SELECT, UPDATE, INSERT, EXECUTE, etc into/from what objects (like tables and/or stored procedures).

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US