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

In article <953689$6u8$1_at_nnrp1.deja.com>,   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.
>

The space freed by a deleted row within a table is not available for reuse until two things happen: 1- the delete is commited (which is true in your case) and 2- the amount of freed space in the block drops the percentage of used space within the block to below the pctused parameter setting. What are the pctfree and pctused settings? They may require adjusting. Also tables with long or LOB columns behaive differently in regards to space management than table without these datatypes.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com
http://www.deja.com/