How to calculate the actual size of a table

here are the example made by Toms kyte…there is the situation maybe lots of people had made the practice,u just stand on the shoulder of giants,and u need to read it carefully,that’s enough..

and thanks a lot, Tom,u did a lot to help countless person..

*********************************************************************************

for a discussion of the use of ANALYZE and DBMS_SPACE to this end.  Basically -- you can
find out how many blocks are allocated to the table (whether used or NOT), how many
blocks have never been used (subtract them from above) and on the blocks that are used --
the average free space.

For example, in the example from above that I linked to -- we see:

ops$tkyte@DEV8I.WORLD> create table t ( x int,
                                 y char(2000) default '*' )
  2  storage ( initial 40k next 40k minextents 5 )
  3  tablespace system;

Table created.

A table that will create ~2k rows for each row inserted.  makes it easy to do the
math

ops$tkyte@DEV8I.WORLD> insert into t (x) values ( 1 );
1 row created.

ops$tkyte@DEV8I.WORLD> analyze table t compute statistics;
Table analyzed.

ops$tkyte@DEV8I.WORLD> compute sum of blocks on report
ops$tkyte@DEV8I.WORLD> break on report
ops$tkyte@DEV8I.WORLD> select extent_id, bytes, blocks
  2    from user_extents
  3   where segment_name = 'T'
  4     and segment_type = 'TABLE'
  5  /

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         2      81920         10
         3     122880         15
         4     163840         20
         0      40960          5
         1      40960          5
                      ----------
sum                           55

ops$tkyte@DEV8I.WORLD> clear breaks
ops$tkyte@DEV8I.WORLD> select blocks, empty_blocks,
  2         avg_space, num_freelist_blocks
  3   from user_tables
  4  where table_name = 'T'
  5  /

    BLOCKS EMPTY_BLOCKS  AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
         1           53       6091                   1

Ok, the above shows us:

o we have 55 blocks allocated to the table
o 53 blocks are totally empty (above the HWM)
o 1 block contains data (the other block is used by the system)
o we have an average of about 6k free on each block used.

Therefore, our table

o consumes 1 block
o of which  1block * 8k blocksize - 1 block * 6k free = 2k is used for our data.

Now, lets put more stuff in there...


ops$tkyte@DEV8I.WORLD> insert into t (x)
  2  select rownum
  3    from all_users
  4   where rownum < 50
  5  /
49 rows created.

ops$tkyte@DEV8I.WORLD> analyze table t compute statistics;
Table analyzed.

ops$tkyte@DEV8I.WORLD> compute sum of blocks on report
ops$tkyte@DEV8I.WORLD> break on report
ops$tkyte@DEV8I.WORLD> select extent_id, bytes, blocks
  2    from user_extents
  3   where segment_name = 'T'
  4     and segment_type = 'TABLE'
  5  /

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         2      81920         10
         3     122880         15
         4     163840         20
         0      40960          5
         1      40960          5
                      ----------
sum                           55

ops$tkyte@DEV8I.WORLD> clear breaks
ops$tkyte@DEV8I.WORLD> select blocks, empty_blocks,
  2         avg_space, num_freelist_blocks
  3    from user_tables
  4   where table_name = 'T'
  5  /

    BLOCKS EMPTY_BLOCKS  AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
        19           35       2810                   3

Ok, the above shows us:

o we have 55 blocks allocated to the table (still)
o 35 blocks are totally empty (above the HWM)
o 19 blocks contains data (the other block is used by the system)
o we have an average of about 2.8k free on each block used.

Therefore, our table

o consumes 19 blocks of storage in total.
o of which  19 blocks * 8k blocksize - 19 block * 2.8k free = 98k is used for our data.

Given our rowsize, this is exactly what we expected.