ORA-10631:SHRINK clause should not be specified for this object

As I’ve worked my way through space management requirements on our Data Warehouse I’ve been shrinking objects to reclaim space that is not needed. Today I was shrinking some tables and ran into the error ORA-10631. I received this error because there is a lot of objects that our data team created and I find out afterwards. So, on with the problem.

PROBLEM:

ORA-10631:SHRINK clause should not be specified for this object SOLUTION:

This error is due to the fact that an object you are trying to shrink has an “function-based” index on it. Objects with function-based indexes cannot be shrunk. To reclaim the space you need to drop and readd the function-based index after shrinking the object*.

*Before dropping any indexes make sure you understand what the index is for and have a script prepared to replace the index. Removing an index can affect performance of the query or the application.

SCRIPT:

This script I’ve provided is script to help you identify what and where the “function-based” indexes are. Use at your own discretion.

SELECT obj.owner
,obj.table_name
,(CASE WHEN NVL(idx.cnt, 0) < 1 THEN 'YES' ELSE 'NO' END) shrinkable
FROM dba_tables obj,
(SELECT table_name, COUNT(rownum) cnt
FROM dba_indexes
WHERE index_type LIKE ‘FUN%’
GROUP BY table_name) idx
WHERE obj.table_name = idx.table_name(+)
AND obj.tablespace_name = upper(‘&1’)

Here is an example of how I use this script to shrink table objects by tablespace.

begin
for i in (SELECT obj.owner
,obj.table_name
,(CASE WHEN NVL(idx.cnt, 0) < 1 THEN 'Y' ELSE 'N' END) as shrinkable
FROM dba_tables obj,
(SELECT table_name, COUNT(rownum) cnt
FROM dba_indexes
WHERE index_type LIKE ‘FUN%’
GROUP BY table_name) idx
WHERE obj.table_name = idx.table_name(+)
AND obj.tablespace_name = upper(‘&1’) and NVL(idx.cnt,0) < 1)
loop
execute immediate ‘alter table ‘||i.owner||’.’||i.table_name||’ enable row movement’;
execute immediate ‘alter table ‘||i.owner||’.’||i.table_name||’ shrink space’;
execute immediate ‘alter table ‘||i.owner||’.’||i.table_name||’ disable row movement’;
end loop;
end;
/