Reclaiming Oracle 9i Index Space

[THoey]

Something I have noticed that perplexes me. In 9i, if I have a data tablespace and a corresponding index tablespace, and I do a massive delete from the data side, it doesn't seem like the index tablespace decreases at all.

In 8i, I have a large database that does a nightly purge of 400-600 thousand old records, and then loads another 400-600 thousand new records. The data and the index tablespaces stay consistent of each other. But on a 9i instance, if I do a data purge on the data tablespace, the index doesn't shrink unless I do a rebuild.

Has anyone else noticed this, or is there a setting, or a clause in the delete that I am missing?

Thanks in advance...

Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[THoey]

I gather no one has seen anything like this?

Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[juliane26]

Hi Terry,

I know we had problems freeing index space in 8i in a 24/7 database. you just could not free it without downtime.

In Version 9i there is the option to rebuild indices online, that should give free the space.
Generally Oracle Indices dont give space free after deletions just like that, you have to tell them.

Juliane

 

[THoey]

Thanks for the info. I haven't had any problems in the past, but with the move to 9i, we are seeing it. I am having to run a script every week now that selects all of indexes and builds a set of scripts like the following:

ALTER INDEX IndexName REBUILD;

I ran this for one tablespace yesterday and reclaimed over five GIG of the ten GIG that was shown as being used. It just seems that 9i is not doing any housework for itself and I have to manually do it when I didn't have to before.

Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...