Monish's DBA Blog

Use the following script to find out tablespace name under the mentioned threshold value:

select trunc(s.bytes/f.maxbytes, 2) used_pct, ‘TABLESPACE ‘ || s.tablespace_name || ‘ is ‘ || to_char(trunc(s.bytes/f.maxbytes, 2)*100) || ‘% full’ description from (select tablespace_name, sum(bytes) bytes from dba_segments
group by tablespace_name) s, (select tablespace_name, sum(greatest(nvl(maxbytes, 0), bytes)) maxbytes from dba_data_files group by tablespace_name) f where s.tablespace_name = f.tablespace_name and s.bytes/f.maxbytes >.90;

The threshold value can be changed by changing the last two numeric digits.