Description

The custom Ignite 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.

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

Ignite alert definition