Why inserts are not using space freed by deletes ?

Hi,

We have one table "dbt_c_in_msg" in our production database in which records are inserted during day and it is purged daily in EOD (records older than 14 days). The problem is that the space freed by the deletes is not being used by inserts. As a result, the table is growing in sizing.

SQL> analyze table dbt_c_in_msg compute statistics; Table analyzed.

SQL> select blocks, empty_blocks, avg_space, pct_free, pct_used,num_rows,
  2 avg_row_len, num_freelist_blocks, avg_space_freelist_blocks   3 from user_tables
  4 where table_name = 'DBT_C_IN_MSG'
  5 /

    BLOCKS EMPTY_BLOCKS AVG_SPACE PCT_FREE PCT_USED NUM_ROWS AVG_ROW_LEN
---------- ------------ ---------- ---------- ---------- ----------

     21479          370       3208         10         70
9536        1790
              16321                      3664

SQL> select initial_extent, next_extent,bytes,blocks, extents from user_segments
  2 where segment_name ='DBT_C_IN_MSG'
  3 and segment_type = 'TABLE';

INITIAL_EXTENT NEXT_EXTENT BYTES BLOCKS EXTENTS
-------------- ----------- ---------- ---------- ----------

      28311552 2097152 89497600 21850 30

As you see, the space allocated to the table is around 89M but the actual data is much less (num_rows * avg_row_len = 9536*1790 = 19M approx.).

When the records are deleted and a block is added to the freelist, then is it added to the beginning of the freelist or at the end of the freelist ?

Pls advise how to make Oracle use the space freed by deletes ?

Thanks.

Sent via Deja.com
http://www.deja.com/ Received on Mon Jan 29 2001 - 01:31:20 CST