estimate table size after reorg (using dbms_space)

When table had huge amount of records deleted, you may need a reorg to lower the high water mark and improve full scan performance.

If using ASSM, you may use dbms_space.space_usage to estimate the emptiness of the blocks.

This script checks how many blocks are 0%, 25%,50%,75%,100% filled for all tables of one user, and gives corresponding estimation for the reorg.

works only on ASSM tablespaces
the script assumes that the pct_free will be the same. 

set serveroutput on linesize 132
declare
   l_fs1_bytes number;
   l_fs2_bytes number;
   l_fs3_bytes number;
   l_fs4_bytes number;
   l_fs1_blocks number;
   l_fs2_blocks number;
   l_fs3_blocks number;
   l_fs4_blocks number;
   l_full_bytes number;
   l_full_blocks number;
   l_unformatted_bytes number;
   l_unformatted_blocks number;
   l_used_bytes_l number;
   l_used_bytes_h number;
   l_current_bytes number;
begin
for seg in  (
select segment_space_management,seg.owner,seg.segment_name,segment_type,seg.partition_name,pct_free,bytes
from dba_segments seg join (
select owner,pct_free, cluster_name segment_name,'' partition_name from DBA_CLUSTERS
union all
select owner,pct_free, index_name,'' partition_name from DBA_INDEXES
union all
select owner,pct_free, table_name,'' partition_name from DBA_TABLES
union all
select table_owner,pct_free, table_name,partition_name from DBA_TAB_PARTITIONS
union all
select index_owner,pct_free, index_name,partition_name from DBA_IND_PARTITIONS
union all
select table_owner,pct_free, table_name,partition_name  from DBA_TAB_SUBPARTITIONS
union all
select index_owner,pct_free, index_name,partition_name from DBA_IND_SUBPARTITIONS
) obj on (obj.owner=seg.owner and obj.segment_name=seg.segment_name and (seg.partition_name is null or seg.partition_name=obj.partition_name))
join dba_tablespaces using(tablespace_name)
where segment_space_management='AUTO' and seg.owner = '&owner'
and segment_type in ('TABLE','TABLE PARTITION','TABLE SUBPARTITION','INDEX','INDEX PARTITION','INDEX SUBPARTITION','CLUSTER','LOB')
             )
loop
   dbms_space.space_usage(
      segment_owner      => seg.owner,
      segment_name       => seg.segment_name,
      segment_type       => seg.segment_type,
      partition_name     => seg.partition_name,
      fs1_bytes          => l_fs1_bytes,
      fs1_blocks         => l_fs1_blocks,
      fs2_bytes          => l_fs2_bytes,
      fs2_blocks         => l_fs2_blocks,
      fs3_bytes          => l_fs3_bytes,
      fs3_blocks         => l_fs3_blocks,
      fs4_bytes          => l_fs4_bytes,
      fs4_blocks         => l_fs4_blocks,
      full_bytes         => l_full_bytes,
      full_blocks        => l_full_blocks,
      unformatted_blocks => l_unformatted_blocks,
      unformatted_bytes  => l_unformatted_bytes
      );
   --dbms_output.put_line(' FS1 Blocks = '||l_fs1_blocks||' Bytes = '||l_fs1_bytes);
   --dbms_output.put_line(' FS2 Blocks = '||l_fs2_blocks||' Bytes = '||l_fs2_bytes);
   --dbms_output.put_line(' FS3 Blocks = '||l_fs3_blocks||' Bytes = '||l_fs3_bytes);
   --dbms_output.put_line(' FS4 Blocks = '||l_fs4_blocks||' Bytes = '||l_fs4_bytes);
   --dbms_output.put_line('Full Blocks = '||l_full_blocks||' Bytes = '||l_full_bytes);
   l_used_bytes_l:=0;
   l_used_bytes_h:=0;
   -- full blocks will reorg in between current and current + pctfree
   l_used_bytes_l:=l_used_bytes_l + l_full_bytes ;
   l_used_bytes_h:=l_used_bytes_h + l_full_bytes * (1+seg.pct_free/100) ;
   -- FS1  will reorg in between 75% and 100% its size
   l_used_bytes_l:=l_used_bytes_l + l_fs1_bytes * 0.75 ;
   l_used_bytes_h:=l_used_bytes_h + l_fs1_bytes * 0.99 ;
   -- FS2  will reorg in between 50% and 75% its size
   l_used_bytes_l:=l_used_bytes_l + l_fs2_bytes * 0.50 ;
   l_used_bytes_h:=l_used_bytes_h + l_fs2_bytes * 0.75 ;
   -- FS3  will reorg in between 25% and 50% its size
   l_used_bytes_l:=l_used_bytes_l + l_fs3_bytes * 0.25 ;
   l_used_bytes_h:=l_used_bytes_h + l_fs3_bytes * 0.50 ;
   -- FS4  will reorg in between 0 and 25% its size
   l_used_bytes_l:=l_used_bytes_l + 0 ;
   l_used_bytes_h:=l_used_bytes_h + l_fs4_bytes * 0.25 ;
   l_current_bytes:=l_full_bytes+l_fs1_bytes+l_fs2_bytes+l_fs3_bytes+l_fs4_bytes+l_unformatted_bytes;
   --dbms_output.put_line(seg.segment_type||' '||seg.owner||'.'||seg.segment_name||' '||seg.partition_name||' reorg will be between '||to_char(l_used_bytes_l,'999G999G999')||' and ' ||to_char(l_used_bytes_h,'999G999G999')||' bytes, CURRENT SIZE: '||to_char((l_full_bytes+l_fs1_bytes+l_fs2_bytes+l_fs3_bytes+l_fs4_bytes+l_unformatted_bytes),'999G999G999'));    
   dbms_output.put_line(seg.segment_type||' '||seg.owner||'.'||seg.segment_name||' '||seg.partition_name||' reorg will be between '||round(100*l_used_bytes_l/l_current_bytes)||'% and ' ||round(100*l_used_bytes_h/l_current_bytes)||' % of current size '||round(l_current_bytes/1024/1024)||' Mb');    
end loop;
end;
/

 

SQL>
SQL> define owner=FRANCK
SQL> /
old  36: where segment_space_management='AUTO' and seg.owner = '&owner'
new  36: where segment_space_management='AUTO' and seg.owner = 'FRANCK'

TABLE FRANCK.TEST  reorg will be between 50% and 70 % of current size 50 Mb

PL/SQL procedure successfully completed.