free2.sql
SELECT SUBSTR(TS.NAME,1,12) "Tablespace", to_char(tf.file#,'990') "File ID", to_char(TF.BLOCKS*.0078125,'99999990') "Blocks/MB", to_char(SUM(F.LENGTH*.0078125),'99999990') "Free/MB", to_char(sum(f.length)/tf.blocks*100,'990.99') "Percentage%", to_char(COUNT(*),'990') "Pieces", to_char(MAX(F.LENGTH*.0078125),'99999990') "Largest/MB", to_char(MIN(F.LENGTH*.0078125),'99999990') "Smallest/MB", to_char(ROUND(AVG(F.LENGTH)*.0078125),'99999990') "Average/MB", to_char(SUM(DECODE(SIGN(F.LENGTH-5), -1, F.LENGTH,0)),'99999990') "Dead" FROM SYS.FET$ F, SYS.FILE$ TF, SYS.TS$ TS WHERE TS.TS# = F.TS# AND TS.TS# = TF.TS# AND f.file# = tf.file# GROUP BY TS.NAME,tf.file#, TF.BLOCKS /