Hi Tom

During my exercise to find the space used by a table 'X' I find huge difference of sizes between the following 2 methods:


TAble X has the following
INitial Extent :65K
PCT_FREE :ZERO
MIN_EXTENTS : 1
X is in a Tablespace that is : Extent Management Local AUtoAllocate

When I issue the following to calculate the size of a table:

A)
select sum(bytes) from dba_segments where segment_type='TABLE'
And segment_name='x';

SUM(BYTES)
------------
6020923392 or approx 6GB



B) Your recommended way of computing allocated space:

compute sum of blocks on report
break on report

select extent_id, bytes, blocks
from user_extents
where segment_name = 'X'
and segment_type = 'TABLE';

The sum of blocks is reported to be:734976

select blocks, empty_blocks,
avg_space, num_freelist_blocks
from user_tables
where table_name = 'X';

BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
733845 0 0 0

db_block_size = 8192

734976* 8192= 6020923392
Which is similar to (A)


C)Now the following gives me a totally different number

After analyzing a sample of table x

select avg_row_len from user_tables where table_name='x';

avg_row_len
----------
100

Select count(*) from x;

-----------

110,049,060

So the total space used by the table X is:

100 * 110049060 =11 GB


In A and B I get 6GB of space used by table X but in C the total space is 11 GB. WHat is contributing to the 5GB . The overhead cannot be so huge?

Regards
TS