Re: Table keeps growing inspite of purging

From: AMARENDRA B NETTEM <nettama_at_charlie.cns.iit.edu> answering Sasha Ostojic about table that keeps growing even though purge (delete) job runs regularl said >>
Hi,
The High Water Mark won't change if you use delete command. <<

1. Try setting the pctused higher to slow growth in extents. Note that upping the pctused will not add existing blocks with available space above the new pctused to the free list so these blocks may go unused for some time.
2. Re-org table with smaller pctfree, if no chained rows, and new larger pctused then watch

You did not list your version of Oracle, but under OPS version 7.1.3 we were able to re-produce the allocation on un-used table extents by Oracle when a sequence was used via stored pl/sql on one of our tables that insert 100,000 rows a day. The test code did not result in unused extents with 7.1.6 which made us very happy. Oracle was unable to reproduce the problem, but they were unable to reproduce our environment.
This only happened to one of our tables, but it happened every week, so we rebuilt it every weekend for several months. Then with 7.1.6 and 7.2.3 no-reorgs.

Hope this helps.
Mark Powell -- The only advise that counts is the advise that you follow so follow your own advise Received on Sat Nov 22 1997 - 00:00:00 CST