2010年1月18日 星期一

ORA-10631 Error when trying to shrink a table

The shrink clause is subject to the following restrictions:
  1. You cannot specify this clause for a cluster, a clustered table, or any object with a LONG column.
  2. Segment shrink is not supported for tables with function
  3. based indexes or bitmap join indexes.
  4. This clause does not shrink mapping tables of index
  5. organized tables, even if you specify CASCADE.
  6. You cannot specify this clause for a compressed table.
  7. You cannot shrink a table that is the master table of an ON COMMIT materialized view.
  8. 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.