formula to calculate row size accurately

[parijat67]

hi all,

i want to know the row size of a given table. i am querying the table called dba_tab_columns and summing up all the data length after grouping by table name. This gives me the total size of the row for that table. The question that i want to ask is is this size the exact row size or does oracle allocate some extra space for each rows? If it does then pls mention how much or what percentage of row size it stores as free space.

The other question i want to ask is how can we calculate the average row size based on the present lot of data for a given table? Its not always that the data for a row would completely fill it up. So a percentage of row space will always be free. If we can calculate the average row space based on the present lot of data then we can be sure that the row size for a table would be say 75% of the total row size and by doing this we can accurately calculate how much the tables will grow in future.

pls give me ur guidance regarding how to do this.

thanks


Parijat Paul

[marcio.68]

To calculate free size on table.
The average of the sizes of column is the placed size divided for the total lines.


Select SUBSTR (owner, 1, 20) "Owner",
SUBSTR (tabela, 1, 20) "Table",
TO_CHAR (SUM (blocos) , '999,999,999') "Blocks Used",
TO_CHAR (SUM (vazios) , '999,999,999') "Blocks Empty",
TO_CHAR (SUM (alocado) / 1048576, '99,999.9999') "Alocad MB",
TO_CHAR (SUM (blocos + vazios) , '999,999,999') "Total Blocks",
TO_CHAR (SUM (alocado) / SUM (blocos + vazios) / 1024, '999,999.9999') "Size Block KB",
TO_CHAR((SUM (alocado) / SUM (blocos + vazios)) * SUM (vazios) / 1048576, '999,999.9999') "Empty MB"
From (
Select owner, table_name tabela, blocks blocos, empty_blocks vazios, 0 alocado
from dba_tables
Where owner not in ('SYS', 'SYSTEM')
union
Select owner, segment_name tabela, 0 blocos, 0 vazios, bytes alocado
from dba_segments
Where owner not in ('SYS', 'SYSTEM') and segment_type = 'TABLE'
)
group by Owner, Tabela
having SUM (blocos) > 0
;

[parijat67]

hi,

thanks for ur post.

i want to know what is the average row size of a given table. pls give me a formula which will read the present data of a given table and calculate the average(filled) row size for that table.

thanks

Paul

[marcio.68]

if your tables have analized, your get column num_rows from dba_tables and divide.