Xref: alice comp.databases.oracle.misc:31850 comp.databases.oracle.server:50653 Path: alice!news-feed.fnsi.net!netnews.com!cyclone.news.idirect.com!island.idirect.com!east1.newsfeed.sprint-canada.net!HME1-2.newsfeed.sprint.ca!newscontent-02.sprint.ca.POSTED!not-for-mail From: "Mikel" Newsgroups: comp.databases.oracle.misc,comp.database.oracle,comp.databases.oracle.server References: Subject: Re: Database vs. Tablespace vs. Datafiles Lines: 123 X-Newsreader: Microsoft Outlook Express 4.72.3110.5 X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3 Message-ID: Date: Sun, 23 May 1999 21:31:20 -0400 X-Complaints-To: abuse@sprint.ca X-Trace: newscontent-02.sprint.ca 927510474 149.99.68.177 (Sun, 23 May 1999 21:47:54 EDT) NNTP-Posting-Date: Sun, 23 May 1999 21:47:54 EDT Organization: Sprint Canada Inc. Greg, you're thinking too hard :) Let's take a different approach. Ok, from the beginning. - A database is just a collection of files that hold data. - A database instance is a set of background process and a memory area (SGA) that lets you access the database - A parameter file contains important info about the database/instance 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 - creates the datafiles for the database - creates the control files for the database - creates the redo log files for the database - creates the SYSTEM tablespace and the SYSTEM rollback segment - creates the data dictionary - creates the users SYS and SYSTEM - specifies the character set that stores data in the database - mounts and opens the database for use 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