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: Database vs. Tablespace vs. Datafiles

Re: Database vs. Tablespace vs. Datafiles

From: Mikel <notreally_at_me.com>
Date: Sun, 23 May 1999 21:31:20 -0400
Message-ID: <ed223.21024$r_1.12077940@newscontent-02.sprint.ca>


Greg, you're thinking too hard :) Let's take a different approach.

Ok, from the beginning.

So

1 - create a parameter file (special text file)
2 - create an instance (ignore for now)
3 - create a database ('create database ....' )

When you create a database , the following occurs

Creating a Database: Example (straight from Oracle docs) The following statement is an example of a CREATE DATABASE statement:

CREATE DATABASE test

     DATAFILE 'test_system' SIZE 10M
     LOGFILE GROUP 1 ('test_log1a', 'test_log1b') SIZE 500K,
     GROUP 2 ('test_log2a', 'test_log2b') SIZE 500K;

The items and information in the example statement above result in creating a database with the following characteristics:

The new database is named TEST.
The SYSTEM tablespace of the new database is comprised of one 10 MB datafile named TEST_SYSTEM.
So, the above is all you have, that's it! The datafile specified in the 'create database....' command is for the SYSTEM tablespace. You must add everything else from here.

So now you have a database, next you want to do something (probably store some data). Lets say you want to store data on two separate business issues (say HR info and Marketing info). So you can see there are two logical data units here (HR and MARKETING). So, you create two logical groups in the database called (you guessed it) TABLESPACES, one tablespace for HR, another tablespace for marketing. Remember these are logical units, so when you create these tablespaces, you must assign one or more datafiles (physical OS files) to actually hold the data.

CREATE TABLESPACE HR
   DATAFILE 'HR01.dbf' SIZE 2M;

HR tablespace has one datafile

CREATE TABLESPACE marketing

   DATAFILE 'MK01.dbf' SIZE 2M ,

                       'MK02.dbf' SIZE 2M ,
                       'MK03.dbf' SIZE 2M ;

Marketing tablespace has 3 datafiles.

So all tablespaces must be created AFTER you create the database. A tablespace created in your TEST database can only exist in your TEST database. Tablespaces cannot be shared between databases (in the basic sense, no OPS or linking issues please). Sooo, You say the script added tablespaces, yes, but they added them to the TEST database we just created, not the 'server'. You see, everything you do now is against the TEST database.

Tablespaces are important! Later on in your dba career, you'll want to do stuff like backup and restore tablespaces, this will give you the flexibility to keep a database open to users while giving attention directly to a tablespace (just trust me on this one).

The script BUILD_DB.SQL is used by Oracle to build the Oracle Starter Database. Look this over to see exactly all the steps involved in building a functioning (albeit not production ready) database.

I hope I did not muddy the waters more, good luck

Mikel

Greg wrote in message ...
>
>I see. In fact, I can add, delete, resize, etc Tablespaces with no
>problem. I even know *why* I'd use them. I'm just having a rough time
>putting together *where and when* to use them. In other words, at what
>point do I "attach" them to the DB? Can I do it after DB creation? Can
>the DB's reference to tablespace(s) be changed on the fly (in other
>words, can I change the rollback TS from "A" to "B".
>
>Now when creating a DB, I'm asked for a DATAFILE that will eventually
>hold the dictionary, schema etc. A DATAFILE ? This is confusing to me -
>why in heck is that initial storage space NOT A TABLESPACE? I'm assuming
>that DATAFILE is already attached to a Tablespace anyway.. what am I
>missing here?
>
>So if I created the DB, now I have to add other TableSpace(s) to it, is
>that right or wrong? (Rollback, index etc.) How do I do that *after* the
>DB is created?
>
>To further confuse me, I saw a script the other day that did something
>like this: [sic]
>
>Create DB using file "filename"
>add tablespace A
>add tablespace B
>
>It just added the tablespace to the server, right? How did it
>automatically add it to the DB? Especially the DB we just created? And
>again, why in heck is that "filename" used instead of a TABLESPACE? I'm
>gonna drive myself to drinkin over this...
>
>Sorry this is so academic, but I'm learning !
>
>Greg
Received on Sun May 23 1999 - 20:31:20 CDT

Original text of this message

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