Reclaim Index tablespace storage space. The fastest approach
Ask_DevSep 20 2010 — edited Sep 23 2010Hi everyone,
My Database version is 10.2.0.4
I have a partitioned table which has partitioned indexes that are located on a tablespace called INDEX01.
the allocated space for the tablespace INDEX01 is about 10 Terabytes, however the used space is around 1.5 Terabytes.
I need to reclaim space at the storage level. I have decided to recreate the INDEX01 tablespace.
I dont have the index creation scripts so I have come up with two approaches
1. to move the index partitions to another tablespace, drop the INDEX01 tablespace, recreate the INDEX01 tablespace and then move the index partitions back.
2. Intentionally mark the index partitions as unusable, drop the INDEX01 tablespace, recreate the INDEX01 tablespace and then rebuild the index partitions.
step 2 seems faster however I need to know if it would work and the impact? ( would the tablespace drop with the index partitions marked unsable) and I hope the original table will not be affected?
regards
samuel