DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3


  1. #1
    Join Date
    Jul 2001


    Hi All,
    I understand that querying the v$datafile will give you the size of the database.
    select sum (bytes/1024/1024/1024) from v$datafile;
    I need to understand this concept from the ground up.

    Can you predefine the size of the database?
    say you want your new database to be 45 Gig.

    Do you specify that at the tablespace level? Can you create a database with 45 Gig at the startup without putting any datafile in it?
    Can some one please help me to understand this concept.

    Thank you

  2. #2
    Join Date
    Apr 2001
    Brisbane, Queensland, Australia
    You can create tablespace during database creation with multiple datafiles that add up to 45 Gb.
    OCP 8i, 9i DBA
    Brisbane Australia

  3. #3
    Join Date
    Aug 2001
    Waterloo, On
    Sizing of database is best handled at the logical Tablespace level.
    When you create database, you define datafile(s) to form the SYSTEM tablespace. Since application & user objects must not be created in the system tablespace, you will need to create other tablespaces as well.

    At the barest minimum, the following tablespaces must be created:

    Rollback segment tablespace: Rollback segments have a short life and they must be created in a tablespace different from where the data resides. The size of this tablespace will depend upon the number & size of rollback segments you wish to create. This will depend upon the number of concurrent transactions, size of each transaction and whether your transactions mainly delete/update or insert data.

    Data tablespace: The size of this table space will depend upon the number of tables, their initial size and growth characteristics. It is better to size each row with full data and estimate the number of rows in the table to calculate each table's rough size and then add up the space required for all the tables.

    Index tablespace: Index must be housed in a different tablespace than data.

    Tools tablespace: To house the OEM repository if you are using this management tool.

    SYSTEM and TOOLS tablespaces may not require much space, so datafiles created for these can be small.

    The storage parameters of database oobjects (Initial extent size, next, pctincrease etc) are crucial for optimum performance and more important than simply sizing the datafiles in a tablespace.

    Then there is the maximum available space in the Disks on the server which are to be kept in mind before creating tablespaces and datafiles for these tablespaces.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width