Oracle 11g table shrink dependency

If a table contain function index, then it can't be shrink directly. Following is the sample error

Resolution is to drop/disabled function index then shrink

SQL> alter table CLFY.USERS shrink space cascade;
alter table CLFY.USERS shrink space cascade
*
ERROR at line 1:
ORA-10631: SHRINK clause should not be specified for this object
SQL> select INDEX_NAME, INDEX_TYPE from dba_indexes where table_owner = 'CLFY' and table_name = 'USERS';

INDEX_NAME INDEX_TYPE
----------- ---------------------
I_USERS NORMAL
I_USERSG NORMAL
I_USERSD2 NORMAL
I_USERSDD FUNCTION-BASED NORMAL



The solution will be to schedule a maintenance window with users, then during the maintenance, perform following (fastest approach):
1. exp CLFY rows=n compress=n tables=USERS file=users
2. In SQL*Plus, drop index i_usersdd
3. alter table CLFY.USERS shrink space cascade;

4. imp CLFY file=users ignore=y full=y

5. exec dbms_stats.gather_table_stats ('CLFY', 'USERS', cascade=> true)

This will automatically re-create the function index.

Additional tips to keep in mind:

1. If several tables with functional indexes are required for house keeping, export them together into dmp file

2. Ensure TEMP tablespace is large enough to create functional index. Verify it has 20% of table size free

3. Ensure archive log destination has sufficient space. Verify it has 20% of table size free

4. If table is larger than 2 GB, verify online redo log are sufficient. Always check v$log view to ensure all of them are archived

Please use following PayPal donate if my post helped