Day: January 9, 2021

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.

ORA-13602: The specified parameter RECOMMEND ALL is not valid for task or object Advisor.

Cause:

Make sure you give the right parameter in the advisor procedure

SQL> BEGIN
  2  dbms_advisor.set_task_parameter(
  3  task_name => 'Advisor',
  4  parameter => 'RECOMMEND ALL', <----- _underscore missed
  5  value => 'TRUE');
  6  END;
  7  /
BEGIN
*
ERROR at line 1:
ORA-13602: The specified parameter RECOMMEND ALL is not valid for task or
object Advisor.
ORA-06512: at "SYS.PRVT_ADVISOR", line 5010
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.PRVT_ADVISOR", line 1248
ORA-06512: at "SYS.PRVT_ADVISOR", line 4902
ORA-06512: at "SYS.DBMS_ADVISOR", line 401
ORA-06512: at line 2

Solution:

Check if you give correct parameter in set task parameter

This parameter stores various attributes that affect workload collection, tuning decisions and reporting.

SQL> BEGIN
  2  dbms_advisor.set_task_parameter(
  3  task_name => 'Advisor',
  4  parameter => 'RECOMMEND_ALL',
  5  value => 'TRUE');
  6  END;
  7  /

PL/SQL procedure successfully completed.