Tablespace size

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