select *
from (select *
from (select a.tablespace_name tablespace_name,
(a.total) as "sum_space",
(b.free_space) as "free_space",
(a.total - b.free_space) as "used_space",
round((a.total - b.free_space) / a.total * 100, 2) as "used_rate"
from (select tablespace_name, sum(bytes / 1024 / 1024) total
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,
sum(bytes / 1024 / 1024) free_space
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by "used_rate" desc)
union all
select *
from (select c.tablespace_name tablespace_name,
(c.total) as "sum_space",
(d.free_space) as "free_space",
(c.total - d.free_space) as "used_space",
round((c.total - d.free_space) / c.total * 100, 2) as "used_rate"
from (select tablespace_name, sum(bytes / 1024 / 1024) total
from dba_temp_files
group by tablespace_name) c,
(select tablespace_name,
sum(free_space / 1024 / 1024) free_space
from dba_temp_free_space
group by tablespace_name) d
where c.tablespace_name = d.tablespace_name
order by "used_rate" desc)) x
order by "used_rate" desc;