Hi There,

I've got into this scenario on a production database which is on 10.2.0.1.
There is a history table which we have run cleanup script to delete records older than 7 days. After the cleanup, the table is around 1.5GB, but within 2 day, its usage has been pushed up to 2GB.
After analyze by dbms_space.space_usage we got:
FS1--0-25% free space within a block
FS2--25-50% free space within a block
FS3--50-75% free space within a block
FS4--75-100% free space within a block
FS1 Blocks = 30 Bytes = 245760
FS2 Blocks = 40 Bytes = 327680
FS3 Blocks = 46 Bytes = 376832
FS4 Blocks = 140121 Bytes = 1147871232
Full Blocks = 3514 Bytes = 28786688

The EXTENT_MANAGEMENT, SEGMENT_SPACE_MANAGEMENT are set to LOCAL and AUTO.
I know the solution is to shrink the table segment. But I am still puzzled by how this happened. It comes to 2 topics I am still trying to find a good answer:
1) How does Oracle use the bitmap to allocate/de-allocate free blocks?
2) How INSERT use the bitmap to look up free blocks? More relative to the problem I have, how INSERT skips all free blocks indicated by bitmap and decide to allocate new extent and push up the HWM?

I've read through the concept guide and DBA admin doc but not able to find what I want, if anyone can point me to the right direction, it will be much appreciated.

Regards
Geoffrey

How are the inserts being done? Are they direct inserts (/*+ APPEND */)?

One thing to consider with such a large delete is that most of the emptied blocks would get pushed back to the data file as dirty blocks, before the commit is done. Not quite sure when Oracle would tidy up the bits that says they are free.

You could try doing a select of the table after the commit forcing a full table scan.

Presumably partitions are not an option for you when purging on a seven day cycle.
Other 'weird' stuff would be an index-organized table where there's less choice about what blocks to use.

Gary

What is size of pctfree on this table and average length or rows ?

Got back to look into this issue again and found it similar to an Oracle bug:
Bug 4660718: TABLE GROWTH POSSIBLE USING AUTOMATIC SEGMENT MANAGEMENT AND LONG RAW COLUMN

And the work around:
exec dbms_repair.segment_fix_status('SAPR3', 'VBDATA');

seems to do the trick. I guess somehow the PCTFREE has been changed or the bitmap used to maintain the local manage segment are malfunctioned or corrupted.

Hope this bit will help someone in the future if you encounter the similar scenario.

Regards
Geoffrey