Tablespace free and used space details
SELECT df.tablespace_name,df.TotalSpace TotalSpaceMB,ds.usedspace UsedspaceMB,ROUND(df.TotalSpace-ds.usedspace) freespaceMB,
ROUND(100*((df.TotalSpace-ds.usedspace)/Totalspace)) pct_free,SYSDATE
FROM
(SELECT tablespace_name,
ROUND(SUM(bytes) / 1048576) TotalSpace
FROM dba_data_files
GROUP BY tablespace_name
) df,
(SELECT ROUND(SUM(bytes)/(1024*1024)) usedspace, tablespace_name
FROM dba_segments
GROUP BY tablespace_name) ds
WHERE df.tablespace_name = ds.tablespace_name;
Datafiles details for a particular tablespace
SELECT file_name,tablespace_name,file_id,((bytes)/ 1048576) TotalSpace
FROM dba_data_files WHERE tablespace_name=&Tablespace_name
GROUP BY tablespace_name,file_name,bytes,file_id
ORDER BY file_id