Oracle Expert

Here is a very useful query to highlight which tables/indexes would benefit from a reorg/rebuild or a shrink

SELECT segment_name, segment_type, round(allocated_space/1024/1024,3) alloc_mb ,round(used_space/1024/1024,3) used_mb ,
round(reclaimable_space/1024/1024,3) reclaimable_mb, round(reclaimable_space/allocated_space*100,3) pct_wastage
FROM TABLE(dbms_space.asa_recommendations())
where segment_owner=’HILDA’
order by 6 desc