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

Most of cases ORA-10631 found during shrink a table. See below example:

SQL> alter table MM.DAILY_REPORT shrink space;
alter table MM.DAILY_REPORT shrink space
SQL> 

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

This error is due to the fact that an object you are trying to shrink has an “function-based” index on it. Objects with function-based indexes cannot be shrunk. To reclaim the space you need to drop and re-create the function-based index after shrinking the object.

Note:
Here is a handy script to locate highly fragmented tables ( top 10):

select *
  from (select table_name,
               round(((blocks * 8)/1024/1024), 1) "size (GB)",
               round((num_rows * avg_row_len / 1024/1024/1024), 1) "actual_data (GB)",
               (round(((blocks * 8)/1024/1024), 1) -
               round((num_rows * avg_row_len / 1024/1024/1024), 1)) "wasted_space (GB)"
          from dba_tables
         where (round((blocks * 8), 1) >
               round((num_rows * avg_row_len / 1024/1024/1024), 1))
         order by 4 desc)
 WHERE ROWNUM <= 10;

Before shrink, table row enable must be done.

-- To enable/disable row movement followed by shrink

SQL> alter table owner.table_name enable row movement;
SQL> alter table owner.table_name shrink space;
SQL> alter table owner.table_name disable row movement;