col tablespace format a20
set linesize 120
select free.tablespace_name tablespace,
data_files file_cnt,
ceil(tot.total) total_size,
ceil(free.free) free_space,
ceil(tot.total-free.free) used_space,
100-ceil((free.free/tot.total)*100) used_pct,
decode((ceil(10-(free.free/tot.total)*10)),
0,'|..........|',
1,'|+.........|',
2,'|++........|',
3,'|+++.......|',
4,'|++++......|',
5,'|+++++.....|',
6,'|++++++....|',
7,'|+++++++...|',
8,'|++++++++..|',
9,'|+++++++++.|',
10,'|++DANGER++|') usager_charted
from (select tablespace_name,
ceil(sum(bytes)/1048576) total,
count(*) data_files
from sys.dba_data_files
group by tablespace_name) tot,
(select tablespace_name tablespace_name,
ceil(sum(bytes)/1048576) free
from sys.dba_free_space
group by tablespace_name) free
where free.tablespace_name=tot.tablespace_name
order by used_pct;