Re: I still don't understand the database concept but

"James" <james_at_nothing.com> wrote in message news:1045731947.931056_at_ftp.adept.co.za...
> Hi there
> Thanks to everyone for help on my last posting (Database names?) but to a
> certain extent it confused me more.
>
> Firstly I'm using Windows 2k and running Oracle 9 server
>
> I've read
>

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c01i
> ntro.htm#12981
> "Introduction to Oracle Server"
>
> From my understanding or this document: You have an Oracle Server, which
has
> databases. The Databases are made up of Logical and Physical structures.
> A database is divided into logical storage units called tablespaces, which
> group related logical structures together.
> And Datafiles physically store the data of all logical structures in the
> table space.

Hi James,

The above is correct however I get the feeling you don't quite understand. Let's see if this helps.

An Oracle "Database" is "physically" made up of the following file structures:

The Data Files. These are the files that actually contain the "data" of your database. So they contain all the tables, indexes, rollback segments, data dictionary objects, etc., etc.

The Redo Log Files. These contain a chronological list of all changes made in the database and are used by Oracle for recovery purposes.

The Control Files. These contain a "physical map" of the database and is used by Oracle to determine the location and physical components of the above listed Data and Redo Log files. They also contain "policing" data, to let Oracle know the current database name, current checkpoint information, current files statuses etc., etc. They also contain "backup/recovery" information as used and documented by Oracle through RMAN. And other bits 'n' pieces.

The SPFILE. Contains the various parameters used for the configuration of the database and it's related instance(s).

That's the "Physical" database.

An Oracle database is "logically" made up of the following structures.

Effectively, a database is logically made up of "Tablespaces". A tablespace is simply a storage area that is used to store "things" in the database. A tablespace is physically made up of one or more of the data files described above. A tablespace can be associated with (or made up of) many data files but an individual data file can only belong to the one tablespace. So when we create something in Oracle (eg a table) we need to decide "where" to store it and the where is always a tablespace (and Oracle decides where within the tablespaces associated data file to put things).

So a tablespace simply is used to store a collection of these things or in Oracle speak these "Segments". Segments are simply the various data objects that can be created in Oracle (tables, indexes, clusters, rollback/undo, temps, partitions, LOBs , etc., etc.). A tablespace can store many segments and have a portion of itself free or unused but an individual segment can only belong to the one tablespace.

A segment is made up of one or more extents. Note that a segment can consist of many extents but an individual extent can only belong to the one segment. An extent is made up of a number of contiguous database blocks (from Oracle's perspective anyway). A database block is effectively the smallest logical component of a database and is in turn made up of a number of O/S blocks. So a physical data file can also be viewed as being made of a number of logical database blocks which is what in turn the tablespace is ultimately made up of. We come full circle.

Note also that a segment must be "created" by a database user and must belong or is "owned" by a database user. The various collection of "things" owned by a particular user is called a "schema". A schema is therefore simply a collection of database segments (and other "things such as stored programs, database links, etc. etc.) owned by a particular user. And so therefore a database can also be viewed as logically being a collection of database schemas. The term user, owner and schema are almost interchangeable.

So that's the database. So when you talk about finding out what databases are currently on your server, one way is to look for the various physical files that your databases are built on (although this could be rather tedious and non conclusive).

An Oracle "Instance" on the other hand is what runs, maintains and manages the Oracle database. It primarily is made up of two major components.

The SGA (System/Shared Global Area) which is an area of memory allocated by Oracle to manage the running of the database. It in turn is made up of various components such as the Shared Pool, Buffer Caches, Redo Log Buffer, Java Pool, Large Pool, etc., etc. and it's where Oracle does all it's processing. All "users" of the database must have access to the SGA, that's why it's "shared".

Various background processes that run under a single thread process on Windows. These background processes include System Monitor, Process Monitor, Database Writers, Log Writer etc. etc. These processes can be started automatically by starting the associated database "service" which is created by Oracle when creating the database through the ODCA or manually via the oradim utility.

Therefore when determining the various databases on your server, you can also look up the various Oracle database services that have been created (probably the easiest way).

>
> Now the question I have is how do I find out all the names of these
> databases? Forexample I know that the Database I created when I setup the
> server is "Maindb". I only have one on my server but I want to know for
the
> situation where I could have multiple!

See what Oracle database services have been created in Services and it will at least get you close. See which ones you can ultimately start to be closer still.

>
> Am I still missing the plot? Sorry that I'm just not understanding this
> concept, but its probably my stupidity shining through! ( or my youth!)
I'm
> very new to Oracle and all you help is much appriciated.
>

It all takes time. Hopefully, the above might help your understanding.

Or totally confuse you senseless ...

Good Luck

Richard Received on Thu Feb 20 2003 - 07:45:32 CST