Hi,

I have the following script to identify the segments having trouble in throwing next extent.

SELECT
seg.owner,
seg.segment_name,
seg.segment_type,
seg.tablespace_name,
seg.next_extent/1024 next_extent
FROM dba_segments seg
WHERE seg.next_extent IS NOT NULL
AND NOT EXISTS
(select tablespace_name
from dba_free_space free
where free.tablespace_name = seg.tablespace_name
and bytes >= seg.next_extent)

I am not sure if there is any fault with this script. I configured this script to run every 12hrs on one of my databases and email me with the results. Last friday one of the object failed in allocating the next extent and this script never warned me.

I would really appreciate your help.

Thanks in Advance.