tablespace usage

select avail_tname "TABLESPACE NAME",round(avail_space,2) "ALLOCATED (GB)",round(free_space,2) "FREE (GB)",round((free_space/avail_space)*100,2) "FREE (%)" from (select tablespace_name avail_tname,sum(bytes)/1024/1024/1024 avail_space from dba_data_files group by tablespace_name) a,(select tablespace_name free_tname,sum(bytes)/1024/1024/1024 free_space from dba_free_space group by tablespace_name) bwhere a.avail_tname=b.free_tname order by 4;

----------------------
select ddf.tablespace_name,total/(1024*1024) ttlSize,(total-nvl(freespace,0))/(1024*1024) used, freespace/(1024*1024) freespacefrom (select tablespace_name,sum(bytes) total from dba_data_files group by tablespace_name) ddf, (select tablespace_name ,sum(bytes) freespace from dba_free_space group by tablespace_name) dfs where ddf.tablespace_name=dfs.tablespace_name(+);


select
fs.tablespace_name "Tablespace",(df.totalspace - fs.freespace) "Used MB",fs.freespace "Free MB",df.totalspace "Total MB",round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from(select
tablespace_name,round(sum(bytes) / 1048576) TotalSpace
from
dba_data_files
group by
tablespace_name) df,(select
tablespace_name,round(sum(bytes) / 1048576) FreeSpace
from
dba_free_space
group by
tablespace_name) fs
where
df.tablespace_name = fs.tablespace_name order by 1;