ALERT LOG 모니터링 뷰 (최종)

현 시점 기준 31일 전까지 유의미한 ALERT LOG 조회

WITH VW_MSG_TYPE AS ( 
                      SELECT 1 MESSAGE_TYPE , 'UNKNOWN' TYPE_NAME FROM DUAL
                       UNION ALL
                      SELECT 2 MESSAGE_TYPE , 'INCIDDENT_ERROR' TYPE_NAME FROM DUAL
                       UNION ALL
                      SELECT 3 MESSAGE_TYPE , 'ERROR' TYPE_NAME FROM DUAL
                       UNION ALL
                      SELECT 4 MESSAGE_TYPE , 'WARNING' TYPE_NAME FROM DUAL
                       UNION ALL
                      SELECT 5 MESSAGE_TYPE , 'NOTIFICATION(정상적 알림)' TYPE_NAME FROM DUAL
                       UNION ALL
                      SELECT 6 MESSAGE_TYPE , 'TRACE' TYPE_NAME FROM DUAL
                      )
    , VW_MSG_LEVEL AS (
                      SELECT 1 MESSAGE_LEVEL , 'CRITICAL (긴급)' LEVEL_NAME FROM DUAL
                       UNION ALL
                      SELECT 2 MESSAGE_LEVEL , 'SEVERE (심각)' LEVEL_NAME FROM DUAL
                       UNION ALL
                      SELECT 3 MESSAGE_LEVEL , 'IMPORTANT (중요)' LEVEL_NAME FROM DUAL
                       UNION ALL
                      SELECT 16 MESSAGE_LEVEL, 'NORMAL' LEVEL_NAME FROM DUAL
                      )
SELECT 'PIGWDB' AS DB_NAME -- DB명
     , TO_CHAR(ORIGINATING_TIMESTAMP,'YYYYMMDD HH24:MI:SS') AS ORIG_TIMESTAMP
     , A.MESSAGE_TYPE
     , B.TYPE_NAME
     , A.MESSAGE_LEVEL
     , C.LEVEL_NAME
     , A.PROBLEM_KEY
     , A.MESSAGE_TEXT
  FROM V$DIAG_ALERT_EXT A 
     , VW_MSG_TYPE B
     , VW_MSG_LEVEL C
 WHERE A.MESSAGE_TYPE = B.MESSAGE_TYPE (+)
   AND A.MESSAGE_LEVEL = C.MESSAGE_LEVEL(+)
   AND C.MESSAGE_LEVEL IN (1,2,3) -- NORMAL 빼고
   AND A.ORIGINATING_TIMESTAMP >= SYSDATE - 31
 ORDER BY ORIGINATING_TIMESTAMP DESC;