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

Thread: Size of Datafile?

Hybrid View

  1. #1
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    How can we arrive at a correct size for a datafile in a tablespace, given the description of tables(one varchar2(5), one char(5), date, number(10)); data storage (100 rows inserted daily) in that datafile after creation?

    Can u please help?

    Thanks.

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    datafile size doesn't play much role for database, because its at physical level. Only tablespace size and table size plays primary role for Oracle internals. What I mean is datafile size is at OS level where as tablespace and table is at database level.

    One should calculate table size, tablespace size for their application data and not datafile size. Datafile can be resized. You can add as many datafiles to a tablespace when your tablesapce is running outta space.

    I am not clear with your Q. Hopw this helps.

  3. #3
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    Thanks for the reply.

    I was asked how will I size the tablespace/datafile based on the objects to be stored on them.

    If only one table will be stored in a production scenario on this tablespace/datafile with the following structure...

    name varchar2(35)
    age number(2)
    address char(35)
    dateofbirth date

    This table will have 100 entries(rows) daily.

    Now how will I size the tablespace/datafile?

    Thanks.


  4. #4
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Calulate the average row size(35+2+35+7) 7bytes for date. Multiply with no. of rows and see whats its growth per day and forcast it for howmany days/months/yeers you want to allocate space. Its just rough estmate... refer docs for clear details on calculations.

    Allocate that much size for tablespace. say 200MB.

    Create a datafile with 200MB+2 Oracle_blocks for over head.

    I would advise you to read the tech paper on fragmentation SAFE which talks about space management and fragmentation. You can find in couple of my postings... search for it.

    [Edited by sreddy on 01-23-2001 at 11:16 AM]

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    First, you will have to figure out what the size of each row is. You can either use a convoluted formula from oracle ( [url]http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76956/tables.htm#328[/url] ) or you can insert some sample data, analyze the table, look at the dba_tables.avg_row_len column and multiply by the number of rows you will have.

    That being said, you should always allow more room for your tables than you estimate they will take up. If you figure all your data in a year will be 32M, allow for 64M.

    Create your data files with the autoextend option on and you will have an easier time of managing your space.
    Jeff Hunter

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hi

    Do you have Oracle DBA Handbook? If you do then check chapter6 or 7 I forgot, there is a guide of how to sizing *roughly* tables, indexes, clusters etc. With an example.

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