extent_watcher.sql

来自「介绍oracle dba常用的报表代码,已经异常查找等」· SQL 代码 · 共 43 行

select EXTENTS.Db_Nm,
       EXTENTS.TS,
       EXTENTS.Seg_Owner,
       EXTENTS.Seg_Name,
       EXTENTS.Seg_Type,
       MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate), Blocks, 0)) Blocks,
       MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate - 28), Extents, 0)) Week1,
       MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate - 21), Extents, 0)) Week2,
       MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate - 14), Extents, 0)) Week3,
       MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate - 7), Extents, 0)) Week4,
       MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate), Extents, 0)) Today,
       MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate), Extents, 0)) -
       MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate - 28), Extents, 0)) Change
  from EXTENTS
 where exists /*did this segment show up today?*/
 (select 'x'
          from EXTENTS x
         where x.Db_Nm = EXTENTS.Db_Nm
           and x.TS = EXTENTS.TS
           and x.Seg_Owner = EXTENTS.Seg_Owner
           and x.Seg_Name = EXTENTS.Seg_Name
           and x.Seg_Type = EXTENTS.Seg_Type
           and x.Check_Date = TRUNC(SysDate))
 group by EXTENTS.Db_Nm,
          EXTENTS.TS,
          EXTENTS.Seg_Owner,
          EXTENTS.Seg_Name,
          EXTENTS.Seg_Type
 order by EXTENTS.Db_Nm,
          EXTENTS.TS,
          DECODE(MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate), Extents, 0)) -
                 MAX(DECODE(EXTENTS.Check_Date,
                            TRUNC(SysDate - 28),
                            Extents,
                            0)),
                 0,
                 -9999,
                 MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate), Extents, 0)) -
                 MAX(DECODE(EXTENTS.Check_Date,
                            TRUNC(SysDate - 28),
                            Extents,
                            0))) desc,
          MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate), Extents, 0)) desc