Oracle DBA Script for Checking Tablespace Used/Free Space

This select statement returns tablespace information including total size, used Mb, free Mb and the number of data files used by the tablespace.

SELECT dt.tablespace_name,
dt.contents,
COUNT(DISTINCT ddf.file_id) dataFileCount,
DECODE(SUM(ddf.maxBytes),0,SUM(ddf.bytes)/(1024*1024),SUM(ddf.maxBytes)/(1024*1024)) MBTotal,
DECODE(SUM(ddf.maxBytes),0,(SUM(ddf.bytes)/(1024*1024))-(ROUND(SUM(dfs.bytes)/(1024*1024))),SUM(ddf.bytes)/(1024*1024)) MBUsed,
DECODE(SUM(ddf.maxBytes),0,ROUND(SUM(dfs.bytes)/(1024*1024)),(SUM(ddf.maxBytes)-SUM(ddf.bytes))/(1024*1024)) MBFree
FROM sys.dba_tablespaces dt,
sys.dba_data_files ddf,
( SELECT file_id,
SUM(bytes) bytes
FROM sys.dba_free_space
GROUP BY file_id ) dfs
WHERE dt.tablespace_name = ddf.tablespace_name
AND ddf.file_id = dfs.file_id(+)
GROUP BY dt.tablespace_name, dt.contents
ORDER BY 1;

Simple, but useful