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: Alex <alex.hudghton_at_capgemini.co.uk>
Date: Mon, 24 May 1999 08:36:46 +0100
Message-ID: <7iavip$1u0$1@taliesin.netcom.net.uk>


Greg

See Answers inline

Alex

Greg wrote in message ...
>Alex, Andreas, Pete, Gary -
>
>I REALLY APPRECIATE THE REPLIES! I've still got one nagging question
>however:
>
>1) Let's say I go and "login" to Ora's internal account, which I am
>*assuming* is the system DB ?

There is no 'system' DB - the default database will be the one pointed to by the variable ORACLE_SID
usually set in the Oracle owners profile (UNIX)

>
>2) I create five new tablespaces, each with 2 or 3 datafiles. No
>problem. I'm assuming these tablespaces are just hanging around out
>there not "attached" or being used for anything at all.

If you have created tablespaces then they are part of the database you are using and are immediately
available for use within that database.

>
>3) Now I create a DB called "NEWDB" and attempt to use one of the above
>data files as my system. The system gripes that the file already exists,
>do I want to UN-ATTACH it from it's current tablespace?

You can't do that - see answer to 2
>
>4) Just to be cooperative I say yes, and it whooshes along and creates
>this new database - NEWDB.
>
>5) Now, I've got NEWDB created, but supposedly not mounted or opened?
>But now I want to add more tablespaces to it (index, rollback etc) but
>I'm told I have to add it via specific DATAFILES (which happens to belong
>to a tablespace if they already exist)? So if I have to add index,
>rollback, user space etc by the DATAFILE, THEN WHY THE HECK DO I NEED
>TABLESPACES?
A datafile is just a container for the tablespace - the tablespace can span many datafiles. Oracle cannot
use datafiles to hold objects - it has to have its own method of being able to overcome the maximum
size of a datafile. For example you could potentially have a 5GB table - on most systems this is not possible
because of a 2GB file limit. Oracle can cope with this because it would hold the table in a 10GB(?) tablespace located over 5 or more datafiles.

>6) What good are tablespaces if I'm forced to work at the FILE LEVEL FOR
>DBA WORK?
You're not - you have to work with both

>7) Plz read #6 again - I think that's my biggest hangup...
>
>8) With this new DB up, just how do I add more tablespaces to it? Do I
>switch logins, say to NEWDB, then just add new tablespaces with specific
>names (I.e. "rollback") the DB is expecting?

With the new database - you must first point to it using the variable ORACLE_SID or the correct connect string you can then add tablespaces as required with any name you wish (apart from reserved words) using the
CREATE TABLESPACE nnnn DATAFILE 'xxx' SIZE command

>
>Even more lost - Greg
>
>
>
>
>
>
>
>
>
>
>In article <7i32la$d5l$1_at_taliesin.netcom.net.uk>,
>alex.hudghton_at_capgemini.co.uk says...
>> Greg
>>
>> System with 3 disks - disk1 disk2 disk3
>>
>> CREATE DATABASE - creates system tablespace in datafile system01.dbf (or
>> similar) on disk1
>>
>> CREATE TABLESPACE - creates other tablespaces (rbs, temp, users, data
etc)
>> in data files temp01.dbf, users01.dbf etc on disk specified in data file
>> location
>>
>> Oracle objects - tables, indexes, etc created in Database - tablespace
>> 'space' used up -eventually tablespace is full
>>
>> ALTER TABLESPACE yyy ADD DATAFILE - increases size of tablespace by
adding a
>> datafile on disk specified in data file location.
>>
>> So order of database creation is - Database (for system tablespace and
>> control files)
>> Tablespaces - for
>> rollback segments and data
>> Users - to access the
>> database
>> Objects - tables,
>> indexes, etc.
>>
>> When tablespaces get full add further datafiles to increase their size
>>
>> Regards
>>
>> Alex
>>
>>
>> Greg wrote in message ...
>> >I'm a little confused on this one. I'm totally new to Oracle, so bear
>> >with me!
>> >
>> >A) I've read tons of docs about the basic layout and policies of Oracle.
>> >It's my understanding that the DB is the master of it all, followed in a
>> >pecking order: TableSpaces, DataFiles, then Extents and other OS block
>> >type objects. Right?
>> >
>> >B) But this is what gets me: When I create a DB, I'm asked to assign a
>> >specific DATAFILE in the create script. Why a DATAFILE? Why have I
>> >totally jumped around the TABLESPACE here? Why the heck doesn't the
>> >DataBase CREATE command point to a series of specific TABLESPACEs for
>> >specific uses instead of a DATAFILE? Then let the DBA assign different
>> >DATAFILES to the tablespaces as needed?
>> >
>> >C) I read in Oracle docs that when I create a new DataBase, it wants a
>
>--
>Advertisers using spambots, please don't send to the following addresses:
>president_at_whitehouse.gov vice.president_at_whitehouse.gov
first.lady_at_whitehouse.gov consumerline_at_ftc.gov crc_at_ftc.gov fraud_at_uspis.gov uce_at_ftc.gov rhunt_at_fcc.gov jquello_at_fcc.gov sness_at_fcc.gov rchong_at_fcc.gov customer_at_usps.gov admin_at_HARRIS-MARKETING.COM postmaster_at_HARRIS-MARKETING.COM jmorton_at_SAVOYNET.COM postmaster_at_agis.net abuse_at_agis.net root_at_agis.net dns-admin_at_AGIS.NET noc_at_AGIS.NET hostmaster_at_agis.net pat.connally_at_state.co.us phil_at_agis.net webmaster_at_agis.net
>postmaster_at_localhost info_at_tsf-industries.com protect_at_atg.wa.gov
lemon_at_atg.wa.gov monopoly_at_atg.wa.gov utility_at_atg.wa.gov c4enviro_at_atg.wa.gov emailago_at_atg.wa.gov kea_at_cognigen.com
>document_at_agis.net ip-request_at_agis.net routing_at_agis.net news_at_agis.net
accounting_at_agis.net info_at_agis.net aapp_at_agis.net hr_at_agis.net emailer_at_qlink2info.com simrem_at_answerme.com hostmaster_at_INREACH.COM ap_at_INREACH.COM postmaster_at_EMAILDIRECT.NET abuse_at_EMAILDIRECT.NET root_at_EMAILDIRECT.NET postmaster_at_e-bizness.com abuse_at_e-bizness.com root_at_e-bizness.com comics_at_IAW.ON.CA maxemail_at_UNITED-CBE.ORG domreg_at_EMAILDIRECT.NET domreg_at_HOSTING.NETCOM.COM bobgalena_at_EARTHLINK.NET
>sales_at_marketingmasters.com bizpro_at_SOFTCOM.NET domreg_at_HOSTING.NETCOM.COM
aral_at_ADDITIONALBENEFITS.COM Received on Mon May 24 1999 - 02:36:46 CDT

Original text of this message

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