What I have learned from Tom Kyte - 2

How to calculate the actual size of a table? I was looking at one of your previous listing about vsize and that may be one way of doing column by column but if there is a tool to do at once that will be a great help. and we said... see http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:231414051079 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.