ORA-10631: SHRINK clause should not be specified for this object
Cause:
The solution for this error by oerr utility is not detailed
However,this issue is due to function based indexes existing on the table and we try to shrink the space in table to reduce fragmentation
SQL> !oerr ora 10631
10631, 00000, “SHRINK clause should not be specified for this object”
// *Cause: It is incorrect to issue shrink on the object
// *Action: Verify the object name and type and reissue the command
SQL> alter table test1.sales shrink space;
alter table test1.sales shrink space
*
ERROR at line 1:
ORA-10631: SHRINK clause should not be specified for this object
Solution:
Only way to overcome this error is to drop the index and recreate it
Identify the function index and drop it
SQL> select index_name from dba_indexes where owner='TEST1' and table_name='SALES';
INDEX_NAME
--------------------------------------------------------------------------------
PK_SALES_ID
FN_ORDER_DATE
SQL> set long 1000000
SQL> select dbms_metadata.get_ddl('INDEX','FN_ORDER_DATE') from dual;
DBMS_METADATA.GET_DDL('INDEX','FN_ORDER_DATE')
--------------------------------------------------------------------------------
CREATE INDEX "TEST1"."FN_ORDER_DATE" ON "TEST1"."SALES" (TRUNC("ORDER_DATE"))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
Drop the index
SQL> alter index test1.FN_ORDER_DATE invisible;
Index altered.
SQL> alter table test1.sales shrink space;
alter table test1.sales shrink space
*
ERROR at line 1:
ORA-10631: SHRINK clause should not be specified for this object
SQL> alter index test1.FN_ORDER_DATE unusable;
Index altered.
SQL> alter table test1.sales shrink space;
alter table test1.sales shrink space
*
ERROR at line 1:
ORA-10631: SHRINK clause should not be specified for this object
SQL> drop index test1.FN_ORDER_DATE;
Index dropped.
SQL> alter table test1.sales shrink space;
Table altered.
After space shrink,rebuild the index
SQL> create index fn_order_date on sales(trunc(order_date));
Index created.
