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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s