rem -----------------------------------------------------------------------
rem Filename:   exterror.sql
rem Purpose:    Segments that will cause errors when they try to extent!!!
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

prompt Objects that cannot extend (no space in TS)

column Sname form a40    heading 'Object Name'
column Stype form a15    heading 'Type'
column Size  form 9,999  heading 'Size'
column Next  form 99,999 heading 'Next'
column Tname form a15    heading 'TsName'

select a.owner||'.'||a.segment_name "Sname", 
       a.segment_type               "Stype",
       a.bytes/1024/1024            "Size",
       a.next_extent/1024/1024 "Next", 
       a.tablespace_name "TName"
  from sys.dba_segments a
 where a.tablespace_name not like 'T%MP%'   -- Exclude TEMP tablespaces
   and next_extent * 1 > (                  -- Cannot extend 1x, can change to 2x...
                           select max(b.bytes)
                             from dba_free_space b
                            where a.tablespace_name = b.tablespace_name)
order by 3 desc
/

-- THIS QUERY GIVES THE SAME RESULTS, BUT IS WAY TOO SLOW
--
-- select  a.owner, a.segment_name, b.tablespace_name,
--         decode(ext.extents,1,b.next_extent,
--         a.bytes*(1+b.pct_increase/100)) nextext,
--         freesp.largest
--   from  dba_extents a,
--         dba_segments b,
--         (select owner, segment_name, max(extent_id) extent_id,
--                 count(*) extents
--            from dba_extents
--           group by owner, segment_name
--         ) ext,
--         (select tablespace_name, max(bytes) largest
--            from dba_free_space
--           group by tablespace_name
--         ) freesp
--  where  a.owner=b.owner
--    and  a.segment_name=b.segment_name
--    and  a.owner=ext.owner
--    and  a.segment_name=ext.segment_name
--    and  a.extent_id=ext.extent_id
--    and  b.tablespace_name = freesp.tablespace_name
--    and decode(ext.extents,1,b.next_extent, a.bytes*(1+b.pct_increase/100)) > freesp.largest
-- /