I have the following script to identify the segments having trouble in throwing next extent.
FROM dba_segments seg
WHERE seg.next_extent IS NOT NULL
AND NOT EXISTS
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.
I have just tried your script here and it runs fine, I know I have some extents in my database which is fail and it selects them fine.
what are you using to run the script every 12 hours? and on what platform?
I configured this through OEM to run for every 12 hours. The OMS (126.96.36.199.0) is on a Linux Box and the Target DB is on a Sun Box.