-- 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) |
