-- PUB Contract monitoring:
SELECT
A.QUEUENAME,DECODE(A.PUBCONSTATUS,0,'ERROR',6,'TIME',A.PUBCONSTATUS) STATUS,
COUNT(*) FROM PSAPMSGPUBCON A
WHERE
A.LASTUPDDTTM >= sysdate - 99 AND (A.PUBCONSTATUS IN (0,6)
OR
(A.PUBCONSTATUS = 0 AND A.LASTUPDDTTM < SYSDATE - 1/48))
GROUP BY
A.QUEUENAME, DECODE(A.PUBCONSTATUS,0,'ERROR',6,'TIME',A.PUBCONSTATUS);
--Oper Instance monitoring:
SELECT
A.QUEUENAME,DECODE(A.PUBSTATUS,0,'ERROR',6,'TIME',A.PUBSTATUS) STATUS,
COUNT(*) FROM PSAPMSGPUBHDR A
WHERE
A.LASTUPDDTTM >= sysdate - 99
AND
(A.PUBSTATUS IN (0, 6) OR (A.PUBSTATUS = 0 AND A.LASTUPDDTTM < SYSDATE -
1/48))
GROUP BY
A.QUEUENAME, DECODE(A.PUBSTATUS,0,'ERROR',6,'TIME',A.PUBSTATUS);
--Sub Contract monitoring
SELECT
A.QUEUENAME,DECODE(A.SUBCONSTATUS,0,'ERROR',6,'TIME',A.SUBCONSTATUS) STATUS,
COUNT(*)
FROM
PSAPMSGSUBCON A
WHERE
A.LASTUPDDTTM >= sysdate - 99
AND
(A.SUBCONSTATUS IN (0, 6)
OR
(A.SUBCONSTATUS = 0 AND A.LASTUPDDTTM < SYSDATE - 1/48))
GROUP BY
A.QUEUENAME,
DECODE(A.SUBCONSTATUS,0,'ERROR',6,'TIME',A.SUBCONSTATUS);
Note :
Script updated to check
only last 99 days "error and timeout" messages.
Useful Oracle Timeout format :
|
Time Interval
|
Format
|
|
Now
|
sysdate
|
|
Tomorrow/Next Day
|
sysdate + 1
|
|
Seven Days from
Now
|
sysdate + 7
|
|
One Hour from Now
|
sysdate + 1/24
|
|
Three Hours from
Now
|
sysdate + 3/24
|
|
Half an Hour from
Now
|
sysdate + 1/48
|
|
Ten Minutes from
Now
|
sysdate + 10/1440
|
|
Thirty Seconds
from Now
|
sysdate + 30/86400
|
|
Tomorrow at 12
Midnight
|
trunc(sysdate + 1)
|
|
Tomorrow at 8am
|
trunc(sysdate + 1)
+ 8/24
|
|
Next Monday at 12
Noon
|
next_day(trunc(sysdate),
'MONDAY') + 12/24
|
|
First Day of the
Month at 12 Midnight
|
trunc(last_day(sysdate)
+ 1)
|
|
The Nex Monday,
Wednesday or Friday at 9am
|
trunc(least(next_day(sysdate,''MONDAY''),next_day(sysdate,''WEDNESDAY''),next_day(sysdate,''FRIDAY'')))
+ (9/24)
|