Check Fragmentation in Oracle Database

Check tables in Schema having fragmented space.

Enter schema name in which you find top 50 fragmented tables.

select * from (
select owner,table_name,round((blocks*8),2) "size (kb)" ,
round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
(round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)", round((((round((blocks * 8), 2) - round((num_rows * avg_row_len / 1024), 2)) /
round((blocks * 8), 2)) * 100 - 10 ),2) "reclaimable space % "
from dba_tables
where owner in ('&SCHEMA_NAME' ) and (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
order by 5 desc ) where rownum < 50;

Check in percentage of table fragmentation

select table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",
round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
(round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100 "percentage"
from all_tables WHERE table_name='&TABLE_NAME';

Data in MB

SELECT * FROM (
SELECT owner,
table_name,
ROUND((blocks * 8) / 1024, 2) AS "size (MB)",
ROUND((num_rows * avg_row_len / (1024 * 1024)), 2) AS "actual_data (MB)",
(ROUND((blocks * 8) / 1024, 2) - ROUND((num_rows * avg_row_len / (1024 * 1024)), 2)) AS "wasted_space (MB)",
ROUND((((ROUND((blocks * 8) / 1024, 2) - ROUND((num_rows * avg_row_len / (1024 * 1024)), 2)) /
ROUND((blocks * 8) / 1024, 2)) * 100 - 10 ), 2) AS "reclaimable_space (%)"
FROM dba_tables
WHERE owner IN ('&SCHEMA_NAME')
AND (ROUND((blocks * 8) / 1024, 2) > ROUND((num_rows * avg_row_len / (1024 * 1024)), 2))
ORDER BY 5 DESC
)
WHERE ROWNUM < 50;