Get all tablespace which are filled at least 80% up to maximum.
set pages 999 set lines 400 column TABLESPACE_NAME format a25 column "TOTAL ALLOC (MB)" format 99,999,999 column "TOTAL PHYS ALLOC (MB)" format 99,999,999 column "USED (MB)" format 99,999,999 column "FREE (MB)" format 99,999,999 column "% USED" format 99,999,999select a.tablespace_name, round(a.bytes_alloc / (1024*1024*1024),1) "TOTAL ALLOC (GB)", round(a.physical_bytes / (
1024*1024*1024
),1) "TOTAL PHYS ALLOC (GB)", round(nvl(b.tot_used,0) / (1024*1024*1024
)) "USED (GB)", round((a.bytes_alloc - a .physical_bytes) / (1024*1024*1024
),1) "Total Free (GB)", round((nvl(b.tot_used,0) / a.bytes_alloc)100,1) "% USED"from ( select tablespace_name,
sum(bytes) physical_bytes,
sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from dba_data_files
group by tablespace_name
) a,
( select tablespace_name,
sum(bytes) tot_used
from dba_segments
group by tablespace_name
) b
where a.tablespace_name = b.tablespace_name (+)
and a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
and a.tablespace_name not like 'UNDO%'
and round((nvl(b.tot_used,0) / a.bytes_alloc)100,1) > 80 order by "% USED" DESC;