Sameer, my query worked when I run it individually but I want columns
MAX_FREE,MAX_EXT and MAX_NEXT_EXT result in bytes instead of blocks.
Any idea how to write PLSQL code to get db_block_size dynamically and calculate it.

Thanks.



SQL>select

  2  ts$.name,

  3  max(fet$.length) max_free,

  4  max(seg$.extsize) max_ext,

  5  seg$.extpct pct,

  6  ceil(max(seg$.extsize*((100+seg$.extpct)/100))) max_next_ext,

  7  trunc(max(fet$.length)/ceil(max(seg$.extsize*((100+seg$.extpct)/100))),2) exts_left

  8  from sys.seg$, sys.fet$, sys.ts$

  9  where ts$.ts# = fet$.ts#

 10  and seg$.ts# = fet$.ts#

 11  group by ts$.name,seg$.extpct

 12  having max(fet$.length)/ceil(max(seg$.extsize*((100+seg$.extpct)/100))) < 20

 13  order by 6 ;



NAME                             MAX_FREE    MAX_EXT        PCT MAX_NEXT_EXT  EXTS_LEFT

------------------------------ ---------- ---------- ---------- ------------ ----------

SMS                                 40569      92169         50       138254        .29

TOOLS                                1778       1280          0         1280       1.38



2 rows selected.