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
/