Below Script is used to find the Tablespace utilization details in a Oracle Database
select a.tablespace_name,a.Total_Size_MB,b.Free_Size_MB,
(a.Total_Size_MB-b.Free_Size_MB) Used_Size_MB,
round(((a.Total_Size_MB-b.Free_Size_MB)/a.Total_Size_MB)*100,2) Pct_Used,
round((b.Free_Size_MB/a.Total_Size_MB)*100,2) Pct_Free
--,b.tablespace_name
from
(select
tablespace_name,sum(bytes/1024/1024) Total_Size_MB
from
dba_data_files
group by tablespace_name) a,
(select tablespace_name,sum(bytes/1024/1024) Free_Size_MB from
dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
union all
select d.tablespace_name,
(d.free_mb+d.used_mb) total_mb,
d.free_mb,d.used_mb,
(d.used_mb/(d.free_mb+d.used_mb))*100,(d.free_mb/(d.free_mb+d.used_mb))*100
from
(select
tablespace_name,round(sum(bytes_free/1024/1024),2) free_mb,
round(sum(bytes_used/1024/1024),2) used_mb
from
v$temp_space_header
group by tablespace_name) d
