SQL Statement
select tablespace_name, (2*max_extent) - max_free from
(select s.tablespace_name, s.max_extent, free_space.max_free
from (
select tablespace_name, nvl(max(next_extent),0) max_extent
from dba_segments
group by tablespace_name) s,
(
select t.tablespace_name, nvl(max(f.bytes),0) max_free
from dba_tablespaces t, dba_free_space f
where t.tablespace_name = f.tablespace_name
group by t.tablespace_name) free_space
where s.tablespace_name = free_space.tablespace_name
order by tablespace_name)
order by tablespace_name
Description
The alert will list tablespaces that contain segments with a next exent size * 2 that is larger than the largest chunk of free space available in the tablespace. In other words, the segment will not be able to extend in the near future if it is growing.
