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

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")

Advertisements

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.