SELECT q_name QUEUE, qt.msgid MSG_ID, corrid CORR_ID, priority MSG_PRIORITY, decode(s.subscriber_type,2,'UNDELIVERABLE',
decode(h.dequeue_time, NULL,decode(l.dequeue_time, NULL, decode(state, 0, 'READY',
1, 'WAIT',
2, 'PROCESSED',
3, 'EXPIRED',
8, 'DEFERRED',
10, 'BUFFERED_EXPIRED'),(decode(l.transaction_id, NULL, 'UNDELIVERABLE', 'PROCESSED'))), (decode(h.transaction_id, NULL, 'UNDELIVERABLE', 'PROCESSED')))
) MSG_STATE, cast(FROM_TZ(qt.delay, '-08:00')
at time zone sessiontimezone as date) delay, cast(FROM_TZ(qt.delay, '-08:00')
at time zone sessiontimezone as timestamp) DELAY_TIMESTAMP, expiration, cast(FROM_TZ(qt.enq_time, '-08:00')
at time zone sessiontimezone as date) enq_time, cast(FROM_TZ(qt.enq_time, '-08:00')
at time zone sessiontimezone as timestamp)
enq_timestamp, enq_uid ENQ_USER_ID, qt.enq_tid ENQ_TXN_ID, decode(h.transaction_id, NULL,
decode(l.transaction_id, NULL, TO_DATE(NULL), l.dequeue_time),
cast(FROM_TZ(h.dequeue_time, '-08:00')
at time zone sessiontimezone as date)) DEQ_TIME, decode(h.transaction_id, NULL,
decode(l.transaction_id, NULL, TO_TIMESTAMP(NULL), l.dequeue_time),
cast(FROM_TZ(h.dequeue_time, '-08:00')
at time zone sessiontimezone as timestamp))
DEQ_TIMESTAMP, decode(h.dequeue_user, NULL, l.dequeue_user, h.dequeue_user)
DEQ_USER_ID, decode(h.transaction_id, NULL, l.transaction_id,
h.transaction_id) DEQ_TXN_ID, h.retry_count retry_count, decode (state, 0, exception_qschema,
1, exception_qschema,
2, exception_qschema,
NULL) EXCEPTION_QUEUE_OWNER, decode (state, 0, exception_queue,
1, exception_queue,
2, exception_queue,
NULL) EXCEPTION_QUEUE, user_data, h.propagated_msgid PROPAGATED_MSGID, sender_name SENDER_NAME, sender_address SENDER_ADDRESS, sender_protocol SENDER_PROTOCOL, dequeue_msgid ORIGINAL_MSGID, decode (h.dequeue_time, NULL, decode (l.dequeue_time, NULL,
decode (state, 3, exception_queue, NULL),
decode (l.transaction_id, NULL, NULL, exception_queue)),
decode (h.transaction_id, NULL, NULL, exception_queue))
ORIGINAL_QUEUE_NAME, decode (h.dequeue_time, NULL, decode (l.dequeue_time, NULL,
decode (state, 3, exception_qschema, NULL),
decode (l.transaction_id, NULL, NULL, exception_qschema)),
decode (h.transaction_id, NULL, NULL, exception_qschema))
ORIGINAL_QUEUE_OWNER, decode(s.subscriber_type,2,
'Messages to be cleaned up later',
decode(h.dequeue_time, NULL,
decode(state, 3,
decode(h.transaction_id, NULL,
decode (expiration , NULL ,
'MAX_RETRY_EXCEEDED',
'TIME_EXPIRATION'),
'INVALID_TRANSACTION', NULL,
'MAX_RETRY_EXCEEDED'), NULL),
decode(h.transaction_id, NULL,
'PROPAGATION_FAILURE', NULL)))
EXPIRATION_REASON, decode(h.subscriber#, 0, decode(h.name, '0', NULL,
h.name),
s.name) CONSUMER_NAME, s.address ADDRESS, s.protocol PROTOCOL FROM "WF_NOTIFICATION_IN" qt, "AQ$_WF_NOTIFICATION_IN_H" h LEFT OUTER JOIN "AQ$_WF_NOTIFICATION_IN_L" l ON h.msgid = l.msgid AND h.subscriber# = l.subscriber# AND h.name = l.name AND h.address# = l.address#, "AQ$_WF_NOTIFICATION_IN_S" s WHERE qt.msgid = h.msgid AND ((h.subscriber# != 0 AND h.subscriber# = s.subscriber_id) OR (h.subscriber# = 0 AND h.address# = s.subscriber_id)) AND (qt.state != 7 OR qt.state != 9 ) WITH READ ONLY
SELECT Q_NAME QUEUE
, QT.MSGID MSG_ID
, CORRID CORR_ID
, PRIORITY MSG_PRIORITY
, DECODE(S.SUBSCRIBER_TYPE
, 2
, 'UNDELIVERABLE'
,
DECODE(H.DEQUEUE_TIME
, NULL
, DECODE(L.DEQUEUE_TIME
, NULL
, DECODE(STATE
, 0
, 'READY'
,
1
, 'WAIT'
,
2
, 'PROCESSED'
,
3
, 'EXPIRED'
,
8
, 'DEFERRED'
,
10
, 'BUFFERED_EXPIRED')
, (DECODE(L.TRANSACTION_ID
, NULL
, 'UNDELIVERABLE'
, 'PROCESSED')))
, (DECODE(H.TRANSACTION_ID
, NULL
, 'UNDELIVERABLE'
, 'PROCESSED')))
) MSG_STATE
, CAST(FROM_TZ(QT.DELAY
, '-08:00')
AT TIME ZONE SESSIONTIMEZONE AS DATE) DELAY
, CAST(FROM_TZ(QT.DELAY
, '-08:00')
AT TIME ZONE SESSIONTIMEZONE AS TIMESTAMP) DELAY_TIMESTAMP
, EXPIRATION
, CAST(FROM_TZ(QT.ENQ_TIME
, '-08:00')
AT TIME ZONE SESSIONTIMEZONE AS DATE) ENQ_TIME
, CAST(FROM_TZ(QT.ENQ_TIME
, '-08:00')
AT TIME ZONE SESSIONTIMEZONE AS TIMESTAMP)
ENQ_TIMESTAMP
, ENQ_UID ENQ_USER_ID
, QT.ENQ_TID ENQ_TXN_ID
, DECODE(H.TRANSACTION_ID
, NULL
,
DECODE(L.TRANSACTION_ID
, NULL
, TO_DATE(NULL)
, L.DEQUEUE_TIME)
,
CAST(FROM_TZ(H.DEQUEUE_TIME
, '-08:00')
AT TIME ZONE SESSIONTIMEZONE AS DATE)) DEQ_TIME
, DECODE(H.TRANSACTION_ID
, NULL
,
DECODE(L.TRANSACTION_ID
, NULL
, TO_TIMESTAMP(NULL)
, L.DEQUEUE_TIME)
,
CAST(FROM_TZ(H.DEQUEUE_TIME
, '-08:00')
AT TIME ZONE SESSIONTIMEZONE AS TIMESTAMP))
DEQ_TIMESTAMP
, DECODE(H.DEQUEUE_USER
, NULL
, L.DEQUEUE_USER
, H.DEQUEUE_USER)
DEQ_USER_ID
, DECODE(H.TRANSACTION_ID
, NULL
, L.TRANSACTION_ID
,
H.TRANSACTION_ID) DEQ_TXN_ID
, H.RETRY_COUNT RETRY_COUNT
, DECODE (STATE
, 0
, EXCEPTION_QSCHEMA
,
1
, EXCEPTION_QSCHEMA
,
2
, EXCEPTION_QSCHEMA
,
NULL) EXCEPTION_QUEUE_OWNER
, DECODE (STATE
, 0
, EXCEPTION_QUEUE
,
1
, EXCEPTION_QUEUE
,
2
, EXCEPTION_QUEUE
,
NULL) EXCEPTION_QUEUE
, USER_DATA
, H.PROPAGATED_MSGID PROPAGATED_MSGID
, SENDER_NAME SENDER_NAME
, SENDER_ADDRESS SENDER_ADDRESS
, SENDER_PROTOCOL SENDER_PROTOCOL
, DEQUEUE_MSGID ORIGINAL_MSGID
, DECODE (H.DEQUEUE_TIME
, NULL
, DECODE (L.DEQUEUE_TIME
, NULL
,
DECODE (STATE
, 3
, EXCEPTION_QUEUE
, NULL)
,
DECODE (L.TRANSACTION_ID
, NULL
, NULL
, EXCEPTION_QUEUE))
,
DECODE (H.TRANSACTION_ID
, NULL
, NULL
, EXCEPTION_QUEUE))
ORIGINAL_QUEUE_NAME
, DECODE (H.DEQUEUE_TIME
, NULL
, DECODE (L.DEQUEUE_TIME
, NULL
,
DECODE (STATE
, 3
, EXCEPTION_QSCHEMA
, NULL)
,
DECODE (L.TRANSACTION_ID
, NULL
, NULL
, EXCEPTION_QSCHEMA))
,
DECODE (H.TRANSACTION_ID
, NULL
, NULL
, EXCEPTION_QSCHEMA))
ORIGINAL_QUEUE_OWNER
, DECODE(S.SUBSCRIBER_TYPE
, 2
,
'MESSAGES TO BE CLEANED UP LATER'
,
DECODE(H.DEQUEUE_TIME
, NULL
,
DECODE(STATE
, 3
,
DECODE(H.TRANSACTION_ID
, NULL
,
DECODE (EXPIRATION
, NULL
,
'MAX_RETRY_EXCEEDED'
,
'TIME_EXPIRATION')
,
'INVALID_TRANSACTION'
, NULL
,
'MAX_RETRY_EXCEEDED')
, NULL)
,
DECODE(H.TRANSACTION_ID
, NULL
,
'PROPAGATION_FAILURE'
, NULL)))
EXPIRATION_REASON
, DECODE(H.SUBSCRIBER#
, 0
, DECODE(H.NAME
, '0'
, NULL
,
H.NAME)
,
S.NAME) CONSUMER_NAME
, S.ADDRESS ADDRESS
, S.PROTOCOL PROTOCOL
FROM "WF_NOTIFICATION_IN" QT
, "AQ$_WF_NOTIFICATION_IN_H" H LEFT OUTER JOIN "AQ$_WF_NOTIFICATION_IN_L" L ON H.MSGID = L.MSGID
AND H.SUBSCRIBER# = L.SUBSCRIBER#
AND H.NAME = L.NAME
AND H.ADDRESS# = L.ADDRESS#
, "AQ$_WF_NOTIFICATION_IN_S" S
WHERE QT.MSGID = H.MSGID
AND ((H.SUBSCRIBER# != 0
AND H.SUBSCRIBER# = S.SUBSCRIBER_ID) OR (H.SUBSCRIBER# = 0
AND H.ADDRESS# = S.SUBSCRIBER_ID))
AND (QT.STATE != 7 OR QT.STATE != 9 ) WITH READ ONLY
|
|
|