SUBSTR(S.ALIVeva lUE,1,INSTR(S.ALIVeva lUE,',',1)) ||
(NVL(SUBSTR(S.ALIVeva lUE,INSTR(S.ALIVeva lUE,',',1)+1,
INSTR(S.ALIVeva lUE,',',1,2)-INSTR(S.ALIVeva lUE,',',1)-1),0) + 1)||
SUBSTR(S.ALIVeva lUE,INSTR(S.ALIVeva lUE,',',1,2))
ELSE
S.ALIVeva lUE
END)
END)
WHERE S.STATUS < '9'
WHEN NOT MATCHED THEN
INSERT (S.TABLESPACE_NAME,S.FILE_NAME,S.IGBYTES,S.CURRENTDAYGBYTES,S.LASTDAYSGBYTES,S.CURRENTWEEKGBYTES,S.LASTWEEKSGBYTES,
S.CURRENTMONTHGBYTES,S.LASTMONTHSGBYTES,S.ALIVeva lUE,S.STATUS)
VALUES (X.TABLESPACE_NAME,X.FILE_NAME,X.IGBYTES,0,0,0,0,0,0,(SYSDATE || ',,5'),
(CASE WHEN X.IGBYTES / 1024 / 1024 / 1024 > 31 THEN 2 ELSE 0 END));
/* Update Status*/
UPDATE DATAFILES_GE_MONITORING UNM
SET UNM.STATUS = (CASE
WHEN UNM.IGBYTES < 26 AND NOT EXISTS (SELECT '1'
FROM DATAFILES_GE_MONITORING UNM1
WHERE UNM1.TABLESPACE_NAME = UNM.TABLESPACE_NAME having count(*) > 1 ) THEN
INSTR(UNM.ALIVeva lUE,',',1,2)-INSTR(UNM.ALIVeva lUE,',',1)-1),0) >=
SUBSTR(UNM.ALIVeva lUE,INSTR(UNM.ALIVeva lUE,',',1,2)+1) - 1)
AND (UNM.STATUS = '0' OR UNM.STATUS = '1') THEN
'1'
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) > 6 AND
EXISTS (SELECT '1' FROM DATAFILES_GE_MONITORING UNM1
WHERE UNM1.TABLESPACE_NAME = UNM.TABLESPACE_NAME having count(*) > 1 ) THEN
(CASE WHEN (SELECT NVL(MIN(NVL(SUBSTR(UNM1.ALIVeva lUE,INSTR(UNM1.ALIVeva lUE,',',1)+1,
INSTR(UNM1.ALIVeva lUE,',',1,2)-INSTR(UNM1.ALIVeva lUE,',',1)-1),0)),0)
FROM DATAFILES_GE_MONITORING UNM1
WHERE UNM1.TABLESPACE_NAME = UNM.TABLESPACE_NAME
AND UNM1.STATUS NOT IN ('2','4')) >=
SUBSTR(UNM.ALIVeva lUE,INSTR(UNM.ALIVeva lUE,',',1,2)+1) - 1 THEN
'1'
WHEN NVL(SUBSTR(UNM.ALIVeva lUE,INSTR(UNM.ALIVeva lUE,',',1)+1,