col tablespace_name for a40
col CUR_PCT for a10
col EXT_PCT for a10
select
tablespace,
current_used_mb,
total_mb,
can_extend_mb,
trunc(current_used_mb/total_mb*100,2)||'%' cur_pct,
trunc(current_used_mb/can_extend_mb*100,2)||'%' ext_pct,
count_file
from
(
select
a.tablespace_name tablespace,
trunc((b.total-a.free)/1024/1024,2) current_used_mb,
trunc(b.total/1024/1024,2) total_mb,
trunc(b.extent_total/1024/1024) can_extend_mb,
b.count_file count_file
from
(
select tablespace_name,
sum(nvl(bytes,0)) total,
sum(decode(maxbytes,0,bytes,maxbytes)) extent_total,
count(file_name) count_file
from dba_data_files
where tablespace_name not like '%UNDO%'
group by tablespace_name
) b
left join
(
select
tablespace_name,
sum(bytes) free
from dba_free_space
group by tablespace_name
) a
on a.tablespace_name=b.tablespace_name
)
order by EXT_PCT desc;