Percentage fragmentation for tables higher than 1 giga and more than 50% of fragmentation.
set linesize 500
set pagesize 100
col owner format a10
col table_name format a10
col TOTAL_SIZE format a14
col ACTUAL_SIZE format a14
col FRAGMENTED_SPACE format a14
col Porcentaje_Fragmentado format 99999
select owner,table_name,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE",
round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE", round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) *100 /round(((blocks*8/1024)),2) Porcentaje_Fragmentado
from dba_tables
where owner not in ('SYSTEM','SYS','XDB','EXFSYS','ORSYS','MDSYS','OLAPSYS','SYSMAN','OUTLN','DBSNMP','TSMSYS','WMSYS','CTXSYS','DMSYS','ORDSYS')
and round(((blocks*8/1024)),2) > 0
and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) > 0
and round((num_rows*avg_row_len/1024/1024),2) > 0
and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) >= 1024
and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) *100 /round(((blocks*8/1024)),2) > 50
union
select owner,table_name,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE",
round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE", round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
null
from dba_tables
where owner not in ('SYSTEM','SYS','XDB','EXFSYS','ORSYS','MDSYS','OLAPSYS','SYSMAN','OUTLN','DBSNMP','TSMSYS','WMSYS','CTXSYS','DMSYS','ORDSYS')
and round(((blocks*8/1024)),2) <= 0
and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) <= 0
and round((num_rows*avg_row_len/1024/1024),2) <= 0
and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) >= 1024
and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) *100 /round(((blocks*8/1024)),2) > 50
ORDER BY Porcentaje_Fragmentado desc;
Comments