Size of Datafile?

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.

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.

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.

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]

First, you will have to figure out what the size of each row is. You can either use a convoluted formula from oracle ( http://technet.oracle.com/docs/produ...tables.htm#328 ) 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.

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.