How to Size Oracle database/segment

I am trying to put together some numbers and calculation for Oracle's space handling. These old ways of calculating and over years Oracle has done significant changes in space management.
So my #s may not be 100% accurate but may give you an idea


Table Name: EPTORA1.H_STOCK_EXEC


Table size calculations:
DB Block Size = 8192 (8k)

With the above column datatypes each row would take around 27 bytes. Formula is Scale-precision/2+1 and for date type its 7
So A = 27

In each block, Oracle stores header information which takes around (141 bytes + 2x) where x is # of records in that block
B = 141 + 2x

At the table level, PCTFREE is 2%, space for updates.
C = 2% of 8192 = 163

So Space Available for data = 8192 - (A+B+C) = 8192 - (27+141+2x+163) = 7861 -2x

If we have x rows in one block

7861 -2x = 27x => x= 271 records approx

# of rows = 3380055613 as of 28th Jan

Space required by all rows (3380055613/x) blocks = (3380055613/271)*8192/1024/1024/1024 = 95 GB

In reality, Oracle allocated 97 GB as of 28th Jan

12:12:46 SYS@gptprd2 SQL> select last_analyzed, num_rows, round(blocks*8192/1024/1024/1024) size_gb from dba_tables where table_name = 'H_STOCK_EXEC';

LAST_ANALYZED NUM_ROWS SIZE_GB
-------------------- ---------- ----------
28-JAN-2011 22:04:36 3300080148 97

1 row selected.

This wastage can be due to last row not fitting in the remaining space and get moved to the next block.



Now lets calculate for Index.
Same stuff but only Indexed column. While considering the space for index we need to consider the precision. Index is on

COLUMN_NAME COLUMN_LENGTH
------------------------- -------------
H_STOCK_ENTRY_ID 22


A=22

In each block, Oracle stores header information which takes around (165 bytes). For index header is bit more
B = 165

At the table level, PCTFREE is 2%, space for updates.
C = 2% of 8192 = 163

Index has some additional header information like ROWID etc
D = 9

So Space Available for index data= 8192 - (A+B+C+D) = 8192 - (22+165+163+6) = 7836

# of rows = 3380055613 as of 28th Jan

# of blocks required for these many rows = 3380055613*28/7836

Index is B-Tree and branches has a 10% space overhead. so multiplying that

So the Index size in GB is 1.1*(3380055613*28/7836)*8192/1024/1024/1024 = 101 GB


16:12:20 SYS@gptprd2 SQL> select last_analyzed, num_rows, round(leaf_blocks*8192/1024/1024/1024) size_gb from dba_indexes where table_name = 'H_STOCK_EXEC';

LAST_ANALYZED NUM_ROWS SIZE_GB
-------------------- ---------- ----------
28-JAN-2011 22:05:07 3380055613 97

1 row selected.