Forever growing indexes problem

        I have an interesting problem with Oracle and I was hoping that someone might have some helpful suggestions.

        The context for this problem is a real time process control system. This system runs 24 hours a day, 7 days a week, 365 days a year. There is a third part application that puts the data in question into Oracle tables. These table and there indexes are set up to be a fixed size & not grow (for performance reasons). We have written a program that removes this data from the tables in question periodically.

        The problem is the indexes. Once they get about 40 - 45% full inserts fail with an unable to allocate segment error. The third party package opens cursors on the tables in question once it needs to log data into them. It never closes these cursors, this actually make sense as it will continue to need to log data into these tables. The indexes however do not shrink after data has been removed from the tables, thus we will eventually reach the point where we can't log any more data simply because the indexes need to grow.

        Shutting the system down, dropping the indexes, and recreating will result in smaller indexes.

        Question does anybody have any thoughts on how to fix this problem? We are still running Version 6 but would upgrade to Version 7 if it would help.

        Any thoughts would be appreciated.

Stan

-- 
Stan Brown     stab_at_netcom                                          404-996-6955
Factory Automation Systems
Atlanta Ga.