Wednesday, February 4, 2009

ORA-10631 When Trying To Shrink A Table That Has A Function-based Index

The shrink_clause is subject to the following restrictions:

-You cannot specify this clause for a cluster, a clustered table, or any object with a LONG column.
-Segment shrink is not supported for tables with function-based indexes or bitmap join indexes.
-This clause does not shrink mapping tables of index-organized tables, even if you specify CASCADE.
-You cannot specify this clause for a compressed table.
-You cannot shrink a table that is the master table of an ON COMMIT materialized view.
-Rowid materialized views must be rebuilt after the shrink operation.

In order to shrink segments that have function based indexes , get index ddl , drop the index , shrink the table and recreate the index.

1 comment: