Oracle table space used check

with t as (select t1.tablespace_name,
round(t1.tot / 1024 / 1024, 2) tot,
round(t2.free / 1024 / 1024, 2) free,
round((t1.tot – t2.free) / 1024 / 1024, 2) used,
round((t1.tot – t2.free) / t1.tot * 100, 2) per
from (select tablespace_name, sum(bytes) tot
from dba_data_files
group by tablespace_name
union all
select tablespace_name, sum(bytes)
from dba_temp_files
group by tablespace_name) t1 full outer join
(select tablespace_name, sum(bytes) free
from dba_free_space
group by tablespace_name
union all
select tablespace_name, free_space from dba_temp_free_space) t2
on t1.tablespace_name = t2.tablespace_name)
select tablespace_name,
decode(trunc(tot/1024),0,to_char(tot,’9990.99′)||‘ MB‘,to_char(round(tot/1024,2),’9990.99′)||‘ GB‘) „Total“,
decode(trunc(free/1024),0,to_char(free,’9990.99′)||‘ MB‘,to_char(round(free/1024,2),’9990.99′)||‘ GB‘) „Free(MB)“,
decode(trunc(used/1024),0,to_char(used,’9990.99′)||‘ MB‘,to_char(round(used/1024,2),’9990.99′)||‘ GB‘) „Used(MB)“,
to_char(per, ‚990.99‘) || ‚%‘ „UsedPercent“
from (select 1 id, tablespace_name, tot, free, used, per
from t
union all
select 2 id, ‚ALL‘ tablespace_name, t2.*
from (select tot, free, used, round(used*100 / tot, 2) per
from (select sum(tot) tot, sum(free) free, sum(used) used
from t)) t2)
order by id, „UsedPercent“ desc;