Re: tablespace calculating

"Paul Meier" <stefan.porges_at_berlin.de> wrote in message news:<9opjuj$eb0q9$1_at_ID-109621.news.dfncis.de>...
> Hi and good morning and thanks to kevin for the first hint,
>
> could somebody please help. I dont know, how to calculate the needed
> tablespace.
> Sample table with 30 columns( 1 - column Number and 29 columns char(20) )
> initial data = 3000 rows
> average growth = 10 rows per day
>
> How many kb - tablespace should i furnish and why?
>
> Thanks a lot
>
> Paul

Paul, the correct answer depends in part on your tablespace space management policy. Are you going to use uniform extents (1 extent size per tablespace), a series of extents which are multiples of each other, or do you desire 1 extent per object?

I would say based on a row size of about ((29 [col] * 20 [bytes]) + 4 [number best guess] + overhead of 30 [length bytes] + 3 [row overhead]) 617 bytes

that 617 * 3000 = 1,851,000 + 10 rows per day but is that every day or just Mon - Fri? 365 * 10 * 617 = 401,050 so 1.8M + 0.4M = 2.2M not including Oracle block overhead.

I would say initial = 1M next = 1M pctincrease = 0 looks like a logical choice.

Here is a rough formula for figuring table size adjusting for block overhead:

Abbreviations

AVIL = Available space in block to hold rows
OBS  = Oracle block size
RS   = Row size
Ovhd = Fixed plus variable block overhead
TBR  = Total blocks required

Expected size = (( RS * number of rows) / AVIL ) * OBS) / K or M where K = 1024 and M = 1048576

Figure RS as
 for varchar2 expected number of characters for column  for number where p = number of ditits and s = 0 for positive and 1 for negative

     round((( length((p) + s) / 2)) + 1
 for date use 7
 + 1 byte per column in row
 + 3 byte row overhead per row

Figure number of bytes for block as
pctfree = decimal value of pctfree parameter * OBS

The variable area is mostly made up of 23 bytes per initran area and 2 bytes per row for the row table entry. For 1 to 4 initrans I have calculated row overhead of 86 to 156 bytes so I just use a constant for this value. Try 113 to start.

Figure AVIL as OBS - ovhd - pctfree

Total bytes = number of expected rows * RS TBR = Total Bytes / AVIL
Expected Size = TBR * OBS / 1024 [for K]

This is one way and it is fairly quick and works pretty well. The formula can be improved by adjusting the variable area size for the number of initrans and for the number of expected rows in the block, but using a constant works well for us.

• Mark D Powell --