Re: Question: Database vs TableSpace (please help)

Zoran wrote:
>
> 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):
>
> * Have 1 DB instance (ORCL).
> * Create 4 seperate tablespaces: X_DATA_SPC0
> X_INDEX_SPC0
> Y_DATA_SPC0
> Y_INDEX_SPC0
> in addition to the SYSTEM tablespace already there.
> Use CREATE TABLESPACE
> * Have 2 schemas: PROJ_X & PROJ_Y
> Use CREATE USER
> * Set the default tablespace for PROJ_X to be X_DATA_SPC0
> Set the default tablespace for PROJ_Y to be Y_DATA_SPC0
> Do when creating the USER or use ALTER USER
>
> 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.

An excellent reply from Zoran.

Other points which might be relevant are: . Separate instances can have completely independent backup/restore cycles
. They can also have completely different sets of init.ora parameters if the characteristics of the two [sub-]systems are completely different (e.g. data warehousing versus OLTP)
. Two separate instances can be connected via SQL*Net) into a single logical database if required. With appropriate links, this connections should be invisible to application programmers and users alike. Although this is most often used when the two instances are on separate processors or machines, the principle is the same on a single machine. . Development and production databases should *always* be separate instances (if only because the [shoul] have identical structures with different data. Or do you let your programmers test new code on production data?)
. It is also an advantage to have a separate static reference (or test) database as a separate instance, but this is one of those pieces of advice that are universally agreded on and just as universally ignored.

Chrysalis. Received on Sun Mar 23 1997 - 00:00:00 CST