Re: Why inserts are not using space freed by deletes ?

How are you inserting your data? Some insert methods (i.e. SQL*Loader) will load above the high water mark for the table. So your free space won't get used.

HTH,
Brian

new_dba_at_my-deja.com wrote:
>
> 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
> ---------- ------------ ---------- ---------- ---------- ----------
> -----------
> NUM_FREELIST_BLOCKS AVG_SPACE_FREELIST_BLOCKS
> ------------------- -------------------------
> 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/
 

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================