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

Home -> Community -> Usenet -> c.d.o.server -> Re: Conceptual hang-up: Database v. tablespace v. schema v. etc...

Re: Conceptual hang-up: Database v. tablespace v. schema v. etc...

From: Mike Biggin <mbiggin_at_denr.sa.gov.au>
Date: 1996/11/06
Message-ID: <32800FB5.FF6@denr.sa.gov.au>#1/1

James Reynolds wrote:
>
> I posted a message about a week ago regarding SQL*Loader. I received a
> number of helpful replies. Thanks to all who responded.
>
> Now, for my next question. I'm going around in circles trying to make
> sense of some Oracle terminology: database vs. tablespace vs. schema vs.
> catalog vs. instance vs. services vs. whatever else I forgot.
>
> The word database is thrown all over. It seems to have a different
> meaning on a conceptual level than it does on an implementational level.
> Would you agree with that?
>
> For instace, if you were setting up a database for a library...
>
> You need a schema for books... a half-dozen tables, lots of records.
> Maybe storing about 5K of data for each book.
>
> My idea that this should be considered a tablespace (or catalog?) inside
> a database, as defined by the CREATE DATABASE statement. Additionally, we
> should have other tablespaces inside the same database for audio/video,
> kids stuff, etc... Am I making sense so far?
>
> What if we were then to have a list of borrowers. That really isn't the
> same kind of data... so would that go into a new database, or just a
> seperate tablespace?
>
> If we were then to distribute the database, does that mean that different
> machines would have to house the same file (since a CREATE DATABASE
> creates one big data file)?
>
> Where do instances and services come into play? You can have multiple
> instances of one database (i.e. a test and a production?).
>
> I'm new to much of this. Any guidence would be appreciated.
>
> Thanks.
>
> Jim.

The Oracle7 Server Concepts Manual describes all that Oracle terminology. I would suggest that anyone who needs to understand the basics of an Oracle database should take time out to read this book. However, here is a very brief summary of some terminology:-

An instance is the set of processes running in memory which manages a particular database. Each database has its own instance.

A database is the physical set of files which are managed by the instance. It will consist of control files, tablespace files, redo log files.

A tablespace is a storage area within a database which is used to hold tables, indexes etc. A tablespace is composed of one or more physical files. A database may possess multiple tablespaces. When a database is first created it usually only has a system tablespace, which holds the data dictionary tables etc. It is fairly usual to add additional tablespaces to hold user objects, indexes, rollback segments and for temporary use. The number of tablespaces required may vary from site to site, it is really a design issue.

You may create multiple users on a database. Each user may be assigned to a default tablespace. This indicates where the user's objects are to be stored. Different users may be assigned to the same or different tablespaces, but are logically independant.

BUT don't just take my word for it. PLEASE read the Oracle7 Server Concepts Manual - and you will attain enlightenment!

-- 
Mike Biggin
DENR, South Australian Government
E-mail: mbiggin_at_denr.sa.gov.au
Phone: +61 8 8204 9217; Fax: +61 8 8204 9017

Unless explicitly attributed, the opinions expressed are personal
and not those of DENR or the South Australian Government.
Received on Wed Nov 06 1996 - 00:00:00 CST

Original text of this message

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