This discussion is archived
12 Replies Latest reply: Mar 30, 2013 8:57 AM by Aman.... RSS

when do i need to create table spaces ?

newbi_egy Explorer
Currently Being Moderated
hi all,

i know that any database has one table space and one data file at first creation , but

when do i need to create another table space or data file ? do i need that when oracle tells me that there is no space anymore or what ?


thanks
  • 1. Re: when do i need to create table spaces ?
    Aman.... Oracle ACE
    Currently Being Moderated
    newbi_egy wrote:
    hi all,

    i know that any database has one table space and one data file at first creation , but

    when do i need to create another table space or data file ? do i need that when oracle tells me that there is no space anymore or what ?
    In oracle database, the concept of storage segregation is done by two ways, using the schema and using the tablespace. So assume that you have two applications . One is used for HR related work and another is used for Finance and both kind of data is stored in one single database only. So how would you maintain such requirement? There are two ways to do it. One, you make two different schemas for each of the application and call them, for example HR schema and Fin schema. Now, you can create objects related to each of the application in it's own schema and store it in may be a single tablespace, Users. But this is going to be a little problematic like what would happen if you have lost the data file of that tablespace? You would be losing the data related to both the applications. So the better option would be to use two different tablespaces and store the data related to each application in both of them.

    Understand that tablespace is a logical structure only. The only thing that's physical is going to be those data files which are stored under the tablespace. So if you are just running out of space in a data file of a tablespace, you do not need to create another tablespace. Take it like this that if you are running out of space for your family in the house, you can either make a new house for them or you can even build a new floor for them. Which one would be easier to manage, the new floor since a lot of ground work is already done for you. And that's what is the case if you have run out of the space in the data file. For this,you can simply create a new datafile(building a new floor) or even extend the existing one(construct a new room in the same floor, if you have space) . But creating a new tablespace won't be something that you should be doing for the reason of space being exhausted IMO.

    Aman....
  • 2. Re: when do i need to create table spaces ?
    EdStevens Guru
    Currently Being Moderated
    newbi_egy wrote:
    hi all,

    i know that any database has one table space and one data file at first creation , but
    when do i need to create another table space or data file ? do i need that when oracle tells me that there is no space anymore or what ?


    thanks
    Aman gave a pretty good explanation, but let me add the warning that you should NOT be putting your application data in the SYSTEM or SYSAUX tablespaces. Those are intended for Oracle's own use.
  • 3. Re: when do i need to create table spaces ?
    newbi_egy Explorer
    Currently Being Moderated
    thanks Aman but clarify more please about :

    1- the table space is a logical space , that means that there is a size to that space , which means that it ends ? right ?
    and i think that is the time i need to create another table space ? right ?

    2- i think you said that i can store two shemas' data in one table space ? right ?
    regards
  • 4. Re: when do i need to create table spaces ?
    695836 Journeyer
    Currently Being Moderated
    Hi,
    When you create a tablespace , you specify datafiles associated with that tablespace along with the SIZE clause that how much initial storage you want this tablespace to have.
    When that size is exausted, you need to increase the size of tablespace either by increasing the size of existing datafiles or adding a new one.

    You can associate multiple schemas in same tablespace.

    alter user <username> default tablespace <tablespacename>;
  • 5. Re: when do i need to create table spaces ?
    newbi_egy Explorer
    Currently Being Moderated
    you answered all of my questions except one :

    does not the space of the table space ends ?
    and then i have to create another one ?

    and this too please

    2- is every database has one table space associated with one datafile by default or
    every schema has one table space associated with one datafile by default ?
    or the two statements are wrong ? provide me with the right words please .


    thanks a lot

    Edited by: newbi_egy on Mar 30, 2013 6:26 AM
  • 6. Re: when do i need to create table spaces ?
    695836 Journeyer
    Currently Being Moderated
    does not the space of the table space ends ? 
    and then i have to create another one ?
    Yes, thats why we add new datafiles or increase the size of existing datafiles.
    2- is every database has one table space associated with one datafile by default or 
    every schema has one table space associated with one datafile by default ? 
    or the two statements are wrong ? provide me with the right words please .
    A database can have many tablespaces and many datafiles.
    A tablespace and datafiles have one to many relationships.
    A tablesapce can have multiple datafiles but a datafile can only belong to a particular tablespace.

    A schema is associated to a tablespace is associated to one or more datafiles.
  • 7. Re: when do i need to create table spaces ?
    Paul M. Oracle ACE
    Currently Being Moderated
    does not space of the table space ends ?
    Only if you you exceed the maximum number of files per tablespace (for smallfile tablespaces, normally 1022) or the maximum size for bigfile tablespaces (32 TB for a tablespace with 8 K blocks).

    See Physical Database Limits
  • 8. Re: when do i need to create table spaces ?
    newbi_egy Explorer
    Currently Being Moderated
    i'm so sorry , but you did not answer any of my questions :

    i am trying to be so specific .

    about the first question of mine .
    does not the space of the table space ends ? 
    and then i have to create another one ?
    suppose that the table space allocates 100 mega bytes and has one datafile ,
    when the data or the objects consume the 100 mega bytes , how could i create a new data file or increase it's size !! , the table space has no more space . , so i have to create another table space associated with another datafile , or increase the table space's space first then increase the datafile or create a new data file ? right or wrong ?


    2- the second one
    i'm talking about the "default"
    2- is every database has one table space associated with one datafile by default?
     or
    every schema has one table space associated with one datafile by default ?
    or the two statements are wrong ? provide me with the right words please .
  • 9. Re: when do i need to create table spaces ?
    sybrand_b Guru
    Currently Being Moderated
    1 You can resize a datafile, as well as add a new datafile.
    The correct syntax is in the online documentation you really should try to read.
    2 Every database has a few default tablespaces, some of them are for dedicated purposes.
    Each tablespace will have at least one datafile.
    The default tablespace for schemas is called 'USERS'.

    All of this is in the online documentation. Please try to read it.


    --------------
    Sybrand Bakker
    Senior Oracle DBA
  • 10. Re: when do i need to create table spaces ?
    695836 Journeyer
    Currently Being Moderated
    When the size of tablespace exhauts, you add a new datafile to the same tablespace or increase the size of existing datafile belonging to that same tablespace.
    You don't create another tablespace.

    About your second question, by default oracle has system,sysaux,undo tablespaces containing one datafile.
    You can modify these settings during the create database command.
  • 11. Re: when do i need to create table spaces ?
    695836 Journeyer
    Currently Being Moderated
    By default oracle creates many schemas and by default they have tablespaces with a single datafile.
  • 12. Re: when do i need to create table spaces ?
    Aman.... Oracle ACE
    Currently Being Moderated
    newbi_egy wrote:
    i'm so sorry , but you did not answer any of my questions :

    i am trying to be so specific .

    about the first question of mine .
    does not the space of the table space ends ? 
    and then i have to create another one ?
    suppose that the table space allocates 100 mega bytes and has one datafile ,
    when the data or the objects consume the 100 mega bytes , how could i create a new data file or increase it's size !! , the table space has no more space . , so i have to create another table space associated with another datafile , or increase the table space's space first then increase the datafile or create a new data file ? right or wrong ?


    2- the second one
    i'm talking about the "default"
    2- is every database has one table space associated with one datafile by default?
    or
    every schema has one table space associated with one datafile by default ?
    or the two statements are wrong ? provide me with the right words please .
    Please spend some time reading the Oracle documentation's Concepts guide and Administrator guide which explains all of this in a very great detail. It's available at http://tahiti.oracle.com .

    Aman....

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points