21 February 2018

Tablespace details with Autoextend

select
t1.tablespace_name,
(nvl(t1.Total_size_mb1,0)+nvl(t1.Total_size_mb2,0)+nvl(t1.Total_size_mb3,0)) as tot_size_mb,
(nvl(t1.free_space_mb1,0) + nvl(t2.free_space_mb2,0)) free_size_mb,
round(((nvl(t1.free_space_mb1,0) + nvl(t2.free_space_mb2,0))/(nvl(t1.Total_size_mb1,0)+nvl(t1.Total_size_mb2,0)
+nvl(t1.Total_size_mb3,0)))*100)as percent_free
from
(
select tablespace_name,
round(sum((case when autoextensible='YES' and bytes<=maxbytes then maxbytes else 0 end)/1048576)) as Total_size_mb1,
round(sum((case when autoextensible='YES' and bytes>maxbytes then bytes else 0 end)/1048576)) as Total_size_mb2,
round(sum((case when autoextensible='NO' then bytes else 0 end)/1048576)) as Total_size_mb3,
round(sum((case when autoextensible='YES' and bytesfrom dba_data_files
group by tablespace_name
)t1,
(
select tablespace_name,round(sum(bytes)/1048576) free_space_mb2
from dba_free_space
group by tablespace_name
)t2
where
t1.tablespace_name=t2.tablespace_name(+)
and
round(((nvl(t1.free_space_mb1,0) + nvl(t2.free_space_mb2,0))/(nvl(t1.Total_size_mb1,0)+nvl(t1.Total_size_mb2,0)
+nvl(t1.Total_size_mb3,0)))*100)<=100
order by round(((nvl(t1.free_space_mb1,0) + nvl(t2.free_space_mb2,0))/(nvl(t1.Total_size_mb1,0)+nvl(t1.Total_size_mb2,0)
+nvl(t1.Total_size_mb3,0)))*100);