Freespace report

I've seen several queries floating around to report space used in Oracle tablespaces and datafiles, but I decided to write my own recently, because:

1. I don't care when a datafile is (nearly) full when the tablespace is not (nearly) full.
2. I'm tired of seeing 2gig datafiles being reported when Oracle has datafiles on a system that does not support large files.
3. When the tablespace *is* full, I'd like to see *all* the associated datafiles.
4. I've also seen inline views summing dba_extents to be faster than queries not using inline views (but I'm too lazy to explain the plan).

So the query below reports all datafiles for tablespaces over 90% full. The query does stomp on the datafile name a bit (what sql needs is unix "basename"). It's a shame that sqlplus "compute" doesn't work over multiple columns. It seems to run pretty fast, though.

Just thought I'd share, and see if anyone has something better.

   / Charles J. Fisher                   |"Dig within. There lies the spring /
  /  cfisher_at_rhadmin.org                 | of good: ever dig, and it will   /
 /   http://rhadmin.org                  | ever flow."  -Marcus Aurelius   /
---------------------------------------------------------------------------

set lin 132
set pages 2000

column ts_mb format 9999
column df_mb format 9999
column ts_pct format 99.9
column df_pct format 99.9

break on tablespace_name skip 1
compute sum of df_mb on tablespace_name

select

	full_ts.tablespace_name,
	full_ts.ts_mb,
	full_ts.ts_pct,
	full_df.df_mb,
	full_df.df_pct,
	substr(full_df.file_name,instr(full_df.file_name,'/',1,4) + 1,25)
		file_name
from
	(select
		df.tablespace_name,
		df.bytes / 1024 / 1024 ts_mb,
		round(e.bytes/df.bytes, 4) * 100 ts_pct
	from


		(select tablespace_name, sum(bytes) bytes

			from dba_data_files group by tablespace_name) df,


		(select tablespace_name, sum(bytes) bytes

			from dba_extents group by tablespace_name) e
	where
		df.tablespace_name = e.tablespace_name
		and e.bytes/df.bytes > 0.85) full_ts,
	(select
		df.file_name,
		df.file_id,
		df.bytes / 1024 / 1024 df_mb,
		round(sum(e.bytes) / df.bytes, 4) * 100 df_pct
	from


		(select tablespace_name, file_name, file_id, bytes

			from dba_data_files) df,


		(select file_id, sum(bytes) bytes

			from dba_extents group by file_id) e
	where
		df.file_id = e.file_id
	group by
		df.file_name, df.file_id, df.bytes) full_df,
	(select tablespace_name, file_id from dba_data_files) ts_df
where
	full_ts.tablespace_name = ts_df.tablespace_name
	AND ts_df.file_id = full_df.file_id
order by
	full_ts.tablespace_name, full_df.file_name