Segment Shrink for fragmented tables

we need to do this segment shrinks in an environment where you purge the data consistantly...

create the table as a one time process...

create table OBJECTS_TO_SHRINK
 (owner varchar2(30), table_name varchar2(30), tablespace_name varchar2(30), actual_mb varchar2(30), optimal_mb varchar2(30), claimable_mb varchar2(30));

script the below code to do segment shrink for the fragmented tables..

select * from OBJECTS_TO_SHRINK;
delete from OBJECTS_TO_SHRINK;
commit;

 declare
sql_stmt   VARCHAR2(1000);
sql_stmt_2 VARCHAR2(1000);
begin
sql_stmt := ' insert into OBJECTS_TO_SHRINK (owner,table_name,tablespace_name,actual_mb,optimal_mb,claimable_mb)
 select t.owner,t.table_name, s.tablespace_name,
round(s.bytes/1024/1024) actual_MB,
round((t.num_rows+1) * t.avg_row_len/1024/1024) optimal_MB,
round(s.bytes/1024/1024) - round((t.num_rows+1) * t.avg_row_len/1024/1024) CLAIMABLE_MB
from dba_tables t, dba_segments s
where t.owner = s.owner and s.owner=''USER_SCHEMA''
and  t.table_name = s.segment_name
---and t.table_name = ''ABC''
and round(s.bytes/1024/1024) - round((t.num_rows+1) * t.avg_row_len/1024/1024) > 200
and round(s.bytes/1024/1024) - round((t.num_rows+1) * t.avg_row_len/1024/1024) < 1500
order by CLAIMABLE_MB desc';
execute immediate sql_stmt;
commit;

FOR indd IN
  (select owner,table_name from OBJECTS_TO_SHRINK)
  LOOP
execute immediate  'alter table  '||indd.owner||'.'||indd.table_name||' enable row movement';
execute immediate 'alter table  '||indd.owner||'.'||indd.table_name||' shrink space compact';
execute immediate  'alter table  '||indd.owner||'.'||indd.table_name||' shrink space';
execute immediate  'alter table  '||indd.owner||'.'||indd.table_name||' disable row movement';
execute immediate  'analyze table '||indd.owner||'.'||indd.table_name||' compute statistics';
commit;
end loop;
end;
/

once, the above is completed. do the datafile resizes which should reduce your space....


NOTE:- if you have any functional-based-indexes defined for that table then we cannot shrink that table directly. we need to remove them by saving their sql/script to recreate-again. then do this process.. below is the error you will see if you have any functional-based-indexes.

ERROR at line 1:
ORA-10631: SHRINK clause should not be specified for this object


Hope this helps....