SUBSTR(UNM.ALIVeva lUE,INSTR(UNM.ALIVeva lUE,',',1,2)+1) - 1 THEN
'3'
ELSE
'0'
END)
WHEN (SELECT COUNT(UNM1.FILE_NAME) * 31.998 - SUM(UNM1.IGBYTES) FROM
DATAFILES_GE_MONITORING UNM1
WHERE UNM1.TABLESPACE_NAME = UNM.TABLESPACE_NAME) BETWEEN 3 AND 6 THEN '5'
WHEN (SELECT COUNT(UNM1.FILE_NAME) * 31.998 - SUM(UNM1.IGBYTES)
FROM DATAFILES_GE_MONITORING UNM1
WHERE UNM1.TABLESPACE_NAME = UNM.TABLESPACE_NAME) BETWEEN 0 AND 2 THEN '6'
ELSE UNM.STATUS
END)
WHERE UNM.STATUS < '9';
COMMIT;
SET LINESIZE 2000;
SET TERM OFF VERIFY OFF FEEDBACK OFF PAGESIZE 2000;
SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF;
spool /wins/test/datafile_monitor/2011_05_13_每日数据维护工作一.html;
SELECT ALARM.TABLESPACE_NAME "需处理的表空间名称",
DENSE_RANK() OVER(PARTITION BY ALARM.STATUS,ALARM.TABLESPACE_NAME ORDER BY ALARM.STATUS DESC,
NVL(SUBSTR(ALARM.ALIVeva lUE,INSTR(ALARM.ALIVeva lUE,',',1)+1,
INSTR(ALARM.ALIVeva lUE,',',1,2)-INSTR(ALARM.ALIVeva lUE,',',1)-1),0) ASC,
ALARM.IGBYTES DESC) "处理优先级",
(CASE WHEN (SELECT COUNT(UNM1.FILE_NAME) * 31.998 - SUM(UNM1.IGBYTES)
FROM DATAFILES_GE_MONITORING UNM1
WHERE UNM1.TABLESPACE_NAME = ALARM.TABLESPACE_NAME) > 0 THEN
(CASE WHEN NVL(SUBSTR(ALARM.ALIVeva lUE,INSTR(ALARM.ALIVeva lUE,',',1)+1,
INSTR(ALARM.ALIVeva lUE,',',1,2)-INSTR(ALARM.ALIVeva lUE,',',1)-1),0) > 0 THEN
'报警！需立即处理,否则影响数据正常入库,目前已'|| SUBSTR(ALARM.ALIVeva lUE,
INSTR(ALARM.ALIVeva lUE,',',1)+1,
INSTR(ALARM.ALIVeva lUE,',',1,2)-INSTR(ALARM.ALIVeva lUE,',',1)-1) ||'天未增长！'
ELSE
'报警！！请立即处理！！！'
END)
ELSE '报警！！！请立即处理,该空间达到或超过最大文件自动扩展空间界限,将要影响该空间所有数据正常运行！'
END)
--警告提示级别统计
ELSE
(CASE WHEN (SELECT COUNT(UNM1.FILE_NAME) * 31.998 - SUM(UNM1.IGBYTES)
FROM DATAFILES_GE_MONITORING UNM1
WHERE UNM1.TABLESPACE_NAME = ALARM.TABLESPACE_NAME) > 3 THEN
(CASE WHEN NVL(SUBSTR(ALARM.ALIVeva lUE,INSTR(ALARM.ALIVeva lUE,',',1)+1,
INSTR(ALARM.ALIVeva lUE,',',1,2)-INSTR(ALARM.ALIVeva lUE,',',1)-1),0) > 0 THEN
'警告 ！需按照业务增长量确定是否立刻调整,目前已'|| SUBSTR(ALARM.ALIVeva lUE,
INSTR(ALARM.ALIVeva lUE,',',1)+1,