ORA-10631 When Trying To Shrink A Table That Has A Function-based Index. (Doc ID 732013.1)

ERROR:
SQL> ALTER TABLE APPLSYS.FND_LOBS SHRINK SPACE CASCADE;
ERROR at line 1:
ORA-10631: SHRINK clause should not be specified for this object

Solution:

To implement the solution, please execute the following steps:

1. Get the DDL of the function-based index on the table.

select INDEX_NAME, INDEX_TYPE
  from dba_indexes
 where table_owner = '<owner>'
   and table_name = '<tabname>';

To find  the DDL creation statement :

Sytax:

SQL> select DBMS_METADATA.GET_DDL('INDEX','< Index_name>') from DUAL;

2. Drop the function-based index.
Syntax:

SQL> drop index applsys.<index_name>;

3. Shrink the table.

4. Recreate the index again on the table.

+++++++++++++++++++++++++++++

How to Shrink (make less sparse) a LOB (BASICFILE or SECUREFILE)? (Doc ID 1451124.1)

How to Determine what storage is used in a LOBSEGMENT and should it be shrunk / reorganized? (Doc ID 1453350.1)

Cannot Execute Shrink on ORABPEL.CUBE_INSTANCE Table in Version 10.1.3.4 or Above: ORA-10631 (Doc ID 948898.1)

386341.1

+++++++++++++++++++++++++++++