
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;
/
really gr8 and Thanks…