space_watcher.sql

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

select SPACES.Db_Nm,
       SPACES.TS,
       MAX(DECODE(SPACES.Check_Date,
                  TRUNC(SysDate - 28),
                  ROUND(100 * Sum_Free_Blocks / Sum_File_Blocks),
                  0)) Week1,
       MAX(DECODE(SPACES.Check_Date,
                  TRUNC(SysDate - 21),
                  ROUND(100 * Sum_Free_Blocks / Sum_File_Blocks),
                  0)) Week2,
       MAX(DECODE(SPACES.Check_Date,
                  TRUNC(SysDate - 14),
                  ROUND(100 * Sum_Free_Blocks / Sum_File_Blocks),
                  0)) Week3,
       MAX(DECODE(SPACES.Check_Date,
                  TRUNC(SysDate - 7),
                  ROUND(100 * Sum_Free_Blocks / Sum_File_Blocks),
                  0)) Week4,
       MAX(DECODE(SPACES.Check_Date,
                  TRUNC(SysDate),
                  ROUND(100 * Sum_Free_Blocks / Sum_File_Blocks),
                  0)) Today,
       MAX(DECODE(SPACES.Check_Date,
                  TRUNC(SysDate),
                  ROUND(100 * Sum_Free_Blocks / Sum_File_Blocks),
                  0)) -
       MAX(DECODE(SPACES.Check_Date,
                  TRUNC(SysDate - 28),
                  ROUND(100 * Sum_Free_Blocks / Sum_File_Blocks),
                  0)) Change
  from SPACES, FILES_TS_VIEW FTV
 where SPACES.Db_Nm = FTV.Db_Nm /*same DB name*/
   and SPACES.TS = FTV.TS /*same TS name*/
   and SPACES.Check_Date = ftv.Check_Date /*same check date*/
   and exists /*does ts exist?*/
 (select 'x'
          from SPACES x
         where x.Db_Nm = SPACES.Db_Nm
           and x.TS = SPACES.TS
           and x.Check_Date = TRUNC(SysDate))
 group by SPACES.Db_Nm, SPACES.TS
having /*has percentfree dropped 5 pct?*/
(MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate), ROUND(100 * Sum_Free_Blocks / Sum_File_Blocks), 0)) - MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate - 28), ROUND(100 * Sum_Free_Blocks / Sum_File_Blocks), 0)) > 5) or /*is percentfree less than 10?*/
(MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate), ROUND(100 * Sum_Free_Blocks / Sum_File_Blocks), 0)) < 10)
 order by SPACES.Db_Nm,
          DECODE(MAX(DECODE(SPACES.Check_Date,
                            TRUNC(SysDate),
                            ROUND(100 * Sum_Free_Blocks / Sum_File_Blocks),
                            0)) -
                 MAX(DECODE(SPACES.Check_Date,
                            TRUNC(SysDate - 28),
                            ROUND(100 * Sum_Free_Blocks / Sum_File_Blocks),
                            0)),
                 0,
                 9999,
                 MAX(DECODE(SPACES.Check_Date,
                            TRUNC(SysDate),
                            ROUND(100 * Sum_Free_Blocks / Sum_File_Blocks),
                            0)) -
                 MAX(DECODE(SPACES.Check_Date,
                            TRUNC(SysDate - 28),
                            ROUND(100 * Sum_Free_Blocks / Sum_File_Blocks),
                            0))),
          MAX(DECODE(SPACES.Check_Date,
                     TRUNC(SysDate),
                     ROUND(100 * Sum_Free_Blocks / Sum_File_Blocks),
                     0))