dbms_space.free_space[2]

kes my rows "big" ops$tkyte@8i> insert into t (x) values ( 1 ); 1 row created. i create one row just to use a little space in the table ops$tkyte@8i> analyze table t compute statistics; table analyzed. ops$tkyte@8i> compute sum of blocks on report ops$tkyte@8i> break on report ops$tkyte@8i> 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 40960 5 3 81920 10 4 57344 7 0 40960 5 1 40960 5 ---------- sum 32 this shows that there are 32 blocks allocated in 5 extents to this table (as expected) ops$tkyte@8i> clear breaks ops$tkyte@8i> 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 30 6091 1 since i analyzed the table, i have acccess to the above information. you'll find that it maps exactly to the data below. there are a total of 32 blocks allocated to the table (below and as confirmed by user_extents above). there are 30 empty_blocks (above)/ unused_blocks (below). these are blocks above the hwm. this leaves 2 blocks unaccounted for -- 1 block has data in it, the other has the extent map for the table (the first block of each table is used by the system itself). ops$tkyte@8i> exec show_space( 't' ) free blocks.............................1 total blocks............................32 total bytes.............................262144 unused blocks...........................30 unused bytes............................245760 last used ext fileid....................1 last used ext blockid...................64816 last used block.........................2 pl/sql procedure successfully completed. ops$tkyte@8i> insert into t (x) 2 select rownum 3 from all_users 4 where rownum < 50 5 / 49 rows created. ops$tkyte@8i> commit; commit complete. so now we have 50 rows with 2k each -- i'm using a 8k blocksize so i expect about 3 rows / block. that means about 18 blocks of data plus 1 for the system = about 19 blocks should be "used" now. below i see that i have o 3 blocks on the freelist. they have more space for new inserts (they have not hit their pctused yet and may be inserted into) o 12 unused blocks, leaving 20 blocks "used". since i have 3 on the freelist -- we probably used a little more then the 18 for data -- we used 19 for the 50 rows. we have one for the system -- all accounted for. ops$tkyte@8i> exec show_space( 't' ) free blocks.............................3 total blocks............................32 total bytes.............................262144 unused blocks...........................12 unused bytes............................98304 last used ext fileid....................1 last used ext blockid...................64681 last used block.........................5 pl/sql procedure successfully completed. ops$tkyte@8i> delete from t; 50 rows deleted. ops$tkyte@8i> commit; commit complete. now we can see what a delete does to our utilization. ops$tkyte@8i> exec show_space( 't' ) free blocks.............................19 total blocks............................32 total bytes.............................262144 unused blocks...........................12 unused bytes............................98304 last used ext fileid....................1 last used ext blockid...................64681 last used block.........................5 pl/sql procedure successfully completed. the above shows that the delete simply put all of our blocks on the free list. we have 19 blocks on the free list + 12 unused blocks + 1 system block = 32 blocks. all accounted for. note that the hwm stayed the same -- we don't have 31 unused blocks -- we have 12 as before. the hwm for a table will never decrease unless we..... ops$tkyte@8i> truncate table t; table truncated. ops$tkyte@8i> exec show_space( 't' ) free blocks.............................0 total blocks............................32 total bytes.............................262144 unused blocks...........................31 unused bytes............................253952 last used ext fileid....................1 last used ext blockid...................64816 last used block.........................1 pl/sql procedure successfully completed. truncate it. that puts all of the blocks below the hwm. now we have 31 unused blocks + 1 system block = 32 blocks