select * from
(select s.owner,segment_name,to_char(last_analyzed,'DD-MON-YY hh24:mi:ss') last_analyzed,round(bytes/1024/1024,2) "ACTUAL_SIZE",
round(avg_row_len*num_rows/1024/1024,2) "RIGHT_SIZE",
CASE WHEN round(((bytes/1024/1024)-(avg_row_len*num_rows/1024/1024)*1.1)*100/(bytes/1024/1024),2) > 70 THEN '<span class="threshold-critical">'||round(((bytes/1024/1024)-(avg_row_len*num_rows/1024/1024)*1.1)*100/(bytes/1024/1024),2)||'</span>' ELSE '<span class="threshold-ok">'||round(((bytes/1024/1024)-(avg_row_len*num_rows/1024/1024)*1.1)*100/(bytes/1024/1024),2)||'</span>' END "%WASTAGE",
to_char(o.last_ddl_time,'DD-MON-YY hh24:mi:ss') LAST_DDL_TIME,
t.DEGREE,t.LOGGING,t.tablespace_name
from dba_segments s,dba_tables t,dba_objects o
where s.owner=t.owner
and t.owner=o.owner
and t.table_name=o.object_name
and s.segment_name=t.table_name
and s.bytes/1024/1024 > 1000
and t.owner='TESTDTA'
and t.table_name='F5707002'
ORDER BY "%WASTAGE" desc)
where rownum < 101;
