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?
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.
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.
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.