##  DBA Command Post - chk_nxt_ext.sql


col name       format a20
col Biggest    format 999999 heading "Biggest"
col Smallest   format 999999 heading "Small"
col Average    format 999999 heading "Average"
col Tot_Blocks format 9999   heading "Exts"
col Tot_Free   format 999999 Heading "Total"
col Max_Ext    format 999999 heading "Max Nxt"
col Panic      format a13    heading  Panic

define Blk_Kb=2048 			/* Define Oracle Block Size */

Select T.name, Tot_Blocks, Tot_Free, Smallest, Average, Biggest,
       max(S.extsize*&Blk_Kb) Max_Ext,
       decode(T.name,'RBS','',
         decode(sum(greatest(sign(nvl((S.extsize*&Blk_Kb),0)-Biggest),0)),
           0, 
            decode(sum(greatest(sign(nvl((S.extsize*2*&Blk_Kb),0)-Biggest),0)),
              0, '',
              'Warn  (x' ||
              to_char(sum(greatest(sign(nvl((S.extsize*2*&Blk_Kb),0)
                          - Biggest),0) ) ) || ')' ) ,
              'PANIC (x' ||
              to_char(sum(greatest(sign(nvl((S.extsize*&Blk_Kb),0)-Biggest),0)))
              || ')' )  ) Panic
from   sys.seg$ S,
       sys.ts$ T,
       sys.Free_Blocks F
where  F.ts# = S.ts# (+)
and    F.ts# = T.ts#
group
   by  T.name, Biggest,Smallest,Average,Tot_Blocks,Tot_Free
order
   by  T.name
/