How to shrink space on table with a function-based index?

Question

I have a partitioned table for which I'd like to remove unnecessary free space. Usually I would run commands like this:

ALTER TABLE {table name} MODIFY SUBPARTITION {subpartion name} SHRINK SPACE;    
ALTER TABLE {table name} MODIFY PARTITION {partion name} SHRINK SPACE;  

But I get an error for a particular table because the table has function-based indexes (see Restrictions on the shrink_clause):

ORA-10631: SHRINK clause should not be specified for this object

All of the indexes on my table are local. There are no global indexes. Does anyone know a solution to shrink the free space?

Dropping function-based indexes, shrinking space and re-creating the indexes is not an option. My table is quite big (200 billion rows, resp. 20 TByte) so it would take far too much time. I need to shrink only a few subpartions, not the entire table.

I also tried to disable the function-based indexes on that partition (by using Partial Indexes for Partitioned Tables) but that did not work either.

Answer 1

One idea is to use partition exchange. That should let you shrink the space needed one partition at a time. I can't test this out myself so please test carefully first in a dev environment. There are at least two different ways to do this.

Method 1:

1. Create a new table with the exact same columns and indexes as the old table. The new table should have one partition.
2. Copy all of the data from the partition to shrink to the new table. You can use parallel DML to speed this up. Since you're writing all of the data again there should be far less wasted space in the new copy.
3. If you aren't already using compression on the big table you could apply it to the new table before swapping the partitions.
4. Use ALTER TABLE ... EXCHANGE PARTITION ... WITH TABLE ... to swap the partitions.
5. Truncate the new table.
6. Repeat as necessary.

Method 2:

1. Create a new table with the exact same columns and indexes as the old table. The new table should have one partition.
2. Use ALTER TABLE ... EXCHANGE PARTITION ... WITH TABLE ... to move one partition to the new table. This should work even for partitions with function-based indexes according to Partition Exchange and Database VLDB and Partitioning Guide. Since your indexes are local it shouldn't invalidate indexes on your big table. If you specify the INCLUDING INDEXES option then indexes should be all valid on the new table as well.
3. Reduce the space needed by the new table. You may just want to use Oracle's MOVE functionality. You can try dropping the function-based index, issuing a SHRINK SPACE command, and recreating the index, but recreating the index could result in a lot of unnecessary free space in the new table.
4. If you aren't already using compression on the big table you could apply it to the new table before moving back the partition.
5. Partition exchange with the old table.
6. Repeat as necessary.

I personally like method 1 better. It gives you increased availability on the big table and you should get more predictable results with less work.

One thing to watch out for is there was some kind of issue with statistics at one point with exchanging partitions with function-based indexes. You can find more information about it here.