監控 concurrent異常記錄

監控 Concurrent 異常記錄

SELECT TO_CHAR(r.request_id) req_id,
       DECODE(r.status_code, 'E', 'Error', 'Warning') status,
       SUBSTR(pt.user_concurrent_program_name, 1, 30) program_name,
       TO_CHAR(r.actual_start_date, 'yyyy/mm/dd hh24:mi') start_date,
       TO_CHAR(r.actual_completion_date, 'yyyy/mm/dd hh24:mi') end_date,
       (SELECT UPPER(host_name) FROM v$instance) host_name
  INTO &request_id,
       &status,
       &concurrent_name,
       &start_date,
       &comp_date,
       &host_name
  FROM fnd_concurrent_requests r, fnd_concurrent_programs_tl pt
 WHERE (r.status_code = 'G' OR r.status_code = 'E')
   AND (r.actual_start_date < SYSDATE - 1 / 2 OR
       r.actual_completion_date < SYSDATE - 1 / 2)
   AND (SELECT COUNT(r.status_code) err_cnt
          FROM fnd_concurrent_requests r
         WHERE (r.status_code = 'G' OR r.status_code = 'E')
           AND (r.actual_start_date < SYSDATE - 1 / 2 OR
               r.actual_completion_date < SYSDATE - 1 / 2)) < 2
   AND pt.concurrent_program_id = r.concurrent_program_id
   AND pt.application_id = r.program_application_id
 ORDER BY request_id DESC