Problem: ORA-10631 errors encountered during "SHRINK SPACE" operation

OEM was nice the other day and offered some Segment Adviser Recommendations to shrink a few tables that had become fragmented. However, when I ran the shrink job, it failed.

PROBLEM:

• "ALTER TABLE my_test_table SHRINK SPACE;" results in "ORA-10631: SHRINK clause should not be specified for this object"

SOLUTION:

• Tables that have function-based indexes cannot be compacted using the SHRINK clause
• Apparently, someone forgot to inform OEM of this fact (thanks the bogus recommendations! :-)
• See MetaLink Bug No.:5968539

Here's a SQL statement that shows all the tables in a given schema and whether they are candidates for the SHRINK clause:

SELECT   dt.owner, dt.table_name,
         (CASE
            WHEN NVL(ind.cnt, 0) < 1 THEN 'Y'
            ELSE 'N'
         END) AS can_shrink
    FROM dba_tables dt,
         (SELECT   table_name, COUNT(*) cnt
              FROM dba_indexes di
             WHERE index_type LIKE 'FUNCTION-BASED%'
          GROUP BY table_name) ind
   WHERE dt.table_name = ind.table_name(+)
     AND dt.table_name NOT LIKE 'AQ$%'
     AND dt.table_name NOT LIKE 'BIN$%'
     AND dt.owner = 'NADVI'
ORDER BY 1, 2;