ORA-10631:SHRINK CLAUSE SHOULD NOT BE SPECIFIED FOR THIS OBJECT

Leave a reply

ORA-10631:SHRINK CLAUSE SHOULD NOT BE SPECIFIED FOR THIS OBJECT

I tried to shrink the table TRAN and get the error ORA-10631.
After checking I found the TRAN table has the function based index. So that why the table is not able to do shrink operation;

Problem:
Error occurred when try to shrink the table of the database:

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

Solution:
1. Find function based index present in database.

select  owner, index_name, index_type
from dba_indexes
where  index_type like 'FUNCTION-BASED%'
and owner not in ('XDB','SYS','SYSTEM') and table_name='TRAN';

2. Find the column expression used in function based index

select table_name,index_name,column_expression
from dba_ind_expressions
where  index_name ='TRAN_IDX';

3. Get the DDL for the index.

set heading off;
set echo off;
Set pages 999;
set long 90000;
select dbms_metadata.get_ddl('INDEX','TRAN_IDX','SCOTT') from dual;
------------------------------------------------------------
CREATE INDEX "SCOTT"."TRAN_IDX" ON "IC"."TRAN" ("QUANTITY_NO*1")

4. If table is small then drop the function based index and do the shrink operation on table and recreate it with help of DDL fetched.

-- Drop the function based index present in table

Drop index SCOTT.TRAN_IDX;

-- do the shrink or reorg operation on table
Alter table SCOTT.TRAN ENABLE ROW MOVEMENT;
ALTER TABLE SCOTT.TRAN SHRINK SPACE CASCADE;
ALTERE TABLE SCOTT.TRAN DISABLE ROW MOVEMENT;

--Recreate the index as ddl in step 3
CREATE INDEX "SCOTT"."TRAN_IDX" ON "SCOTT"."TRAN" ("QUANTITY_NO*1")



Leave a Reply