Table Fragmentation
To get Actual, Optimal, Reclaimable Size from the tables of a specific user.
set linesize 200 trimspool on col owner for a22 col table_name for a30 col tablespace_name for a20 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='&owner_name' and t.table_name = s.segment_name and round(s.bytes/1024/1024) - round((t.num_rows+1) * t.avg_row_len/1024/1024) > 200 order by CLAIMABLE_MB desc /
Sample Output:-
From above screenshots, By perform Table reorganization 19gb, 11gb, 3gb …… Space can be reclaim from the tables.
