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.
Best Answer
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:
ALTER TABLE ... EXCHANGE PARTITION ... WITH TABLE ...
to swap the partitions.Method 2:
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 theINCLUDING INDEXES
option then indexes should be all valid on the new table as well.SHRINK SPACE
command, and recreating the index, but recreating the index could result in a lot of unnecessary free space in the new table.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.