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_CONTROL" qt, "AQ$_WF_CONTROL_H" h LEFT OUTER JOIN "AQ$_WF_CONTROL_L" l ON h.msgid = l.msgid AND h.subscriber# = l.subscriber# AND h.name = l.name AND h.address# = l.address#, "AQ$_WF_CONTROL_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 ) UNION ALL SELECT q.name QUEUE, b.msgid MSG_ID, b.corrid CORR_ID, b.priority MSG_PRIORITY, decode (TO_CHAR(b.state), '4', 'IN MEMORY',
'5', 'DEFERRED',
'12', 'EXPIRED INMEMORY',
NULL) MSG_STATE, cast (null as DATE) DELAY, cast (null as TIMESTAMP) DELAY_TIMESTAMP, b.expiration EXPIRATION, cast(FROM_TZ(b.enq_time, '-08:00')
at time zone sessiontimezone as date) ENQ_TIME, cast(FROM_TZ(b.enq_time, '-08:00')
at time zone sessiontimezone as timestamp)
ENQ_TIMESTAMP, b.enq_uid ENQ_USER_ID, cast (null as VARCHAR2(30)) ENQ_TXN_ID, cast (null as DATE) DEQ_TIME, cast(null as TIMESTAMP) DEQ_TIMESTAMP, cast (null as number) DEQ_USER_ID, cast(null as VARCHAR2(30)) DEQ_TXN_ID, b.retry_count RETRY_COUNT, b.exceptionq_schema EXCEPTION_QUEUE_OWNER, b.exceptionq_name EXCEPTION_QUEUE, sys.dbms_aq_bqview.get_adt_payload(b.queue_id, b.msg_num, "SYS"."AQ$_JMS_TEXT_MESSAGE"(NULL, NULL, NULL, NULL)) USER_DATA, cast(null as RAW(16)) PROPAGATED_MSGID, b.sender_name SENDER_NAME, b.sender_address SENDER_ADDRESS, b.sender_protocol SENDER_PROTOCOL, b.dequeue_msgid ORIGINAL_MSGID, cast (null as VARCHAR2(30)) ORIGINAL_QUEUE_NAME, cast(null as VARCHAR2(30)) ORIGINAL_QUEUE_OWNER, decode(b.state, 12, 'TIME_EXPIRATION',
NULL) EXPIRATION_REASON, s.name CONSUMER_NAME, s.address ADDRESS, s.protocol PROTOCOL FROM SYS.qt245562_BUFFER b, all_queues q, "AQ$_WF_CONTROL_S" s WHERE s.subscriber_id = b.subscriber_id AND bitand(s.subscriber_type, 8) != 8 AND bitand(b.state, 4) = 4 AND q.qid = b.queue_id UNION ALL SELECT p.q_name QUEUE, p.msgid MSG_ID, p.corrid CORR_ID, p.priority MSG_PRIORITY, decode (TO_CHAR(b.state), '2', 'SPILLED',
'3', 'DEFERRED SPILLED',
'10', 'EXPIRED SPILLED',
NULL) MSG_STATE, cast(FROM_TZ(p.delay, '-08:00')
at time zone sessiontimezone as date) DELAY, cast(FROM_TZ(p.delay, '-08:00')
at time zone sessiontimezone as timestamp) DELAY_TIMESTAMP, p.expiration EXPIRATION, cast(FROM_TZ(p.enq_time, '-08:00')
at time zone sessiontimezone as date) ENQ_TIME, cast(FROM_TZ(p.enq_time, '-08:00')
at time zone sessiontimezone as timestamp)
ENQ_TIMESTAMP, p.enq_uid ENQ_USER_ID, p.enq_tid ENQ_TXN_ID, cast(FROM_TZ(p.deq_time, '-08:00')
at time zone sessiontimezone as date) DEQ_TIME, cast(FROM_TZ(p.deq_time, '-08:00')
at time zone sessiontimezone as timestamp)
DEQ_TIMESTAMP, p.deq_uid DEQ_USER_ID, p.deq_tid DEQ_TXN_ID, p.retry_count RETRY_COUNT, p.exception_qschema EXCEPTION_QUEUE_OWNER, p.exception_queue EXCEPTION_QUEUE, p.user_data USER_DATA, cast (null as RAW(16))PROPAGATED_MSGID,p.sender_name SENDER_NAME, p.sender_address SENDER_ADDRESS, p.sender_protocol SENDER_PROTOCOL, cast (null as RAW(16)) ORIGINAL_MSGID, cast (null as VARCHAR2(30)) ORIGINAL_QUEUE_NAME, cast (null as VARCHAR2(30)) ORIGINAL_QUEUE_OWNER, decode(b.state, 10, 'TIME_EXPIRATION',
NULL) EXPIRATION_REASON, s.name CONSUMER_NAME, s.address ADDRESS, s.protocol PROTOCOL FROM "AQ$_WF_CONTROL_P" p, SYS.qt245562_BUFFER b, "AQ$_WF_CONTROL_S" s, all_queues q WHERE b.subscriber_id = s.subscriber_id AND bitand(s.subscriber_type, 8) != 8 AND bitand(b.state ,2) = 2 AND p.msgid = b.msgid AND q.qid = b.queue_id and p.q_name = q.name 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_CONTROL" QT
, "AQ$_WF_CONTROL_H" H LEFT OUTER JOIN "AQ$_WF_CONTROL_L" L ON H.MSGID = L.MSGID
AND H.SUBSCRIBER# = L.SUBSCRIBER#
AND H.NAME = L.NAME
AND H.ADDRESS# = L.ADDRESS#
, "AQ$_WF_CONTROL_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 ) UNION ALL SELECT Q.NAME QUEUE
, B.MSGID MSG_ID
, B.CORRID CORR_ID
, B.PRIORITY MSG_PRIORITY
, DECODE (TO_CHAR(B.STATE)
, '4'
, 'IN MEMORY'
,
'5'
, 'DEFERRED'
,
'12'
, 'EXPIRED INMEMORY'
,
NULL) MSG_STATE
, CAST (NULL AS DATE) DELAY
, CAST (NULL AS TIMESTAMP) DELAY_TIMESTAMP
, B.EXPIRATION EXPIRATION
, CAST(FROM_TZ(B.ENQ_TIME
, '-08:00')
AT TIME ZONE SESSIONTIMEZONE AS DATE) ENQ_TIME
, CAST(FROM_TZ(B.ENQ_TIME
, '-08:00')
AT TIME ZONE SESSIONTIMEZONE AS TIMESTAMP)
ENQ_TIMESTAMP
, B.ENQ_UID ENQ_USER_ID
, CAST (NULL AS VARCHAR2(30)) ENQ_TXN_ID
, CAST (NULL AS DATE) DEQ_TIME
, CAST(NULL AS TIMESTAMP) DEQ_TIMESTAMP
, CAST (NULL AS NUMBER) DEQ_USER_ID
, CAST(NULL AS VARCHAR2(30)) DEQ_TXN_ID
, B.RETRY_COUNT RETRY_COUNT
, B.EXCEPTIONQ_SCHEMA EXCEPTION_QUEUE_OWNER
, B.EXCEPTIONQ_NAME EXCEPTION_QUEUE
, SYS.DBMS_AQ_BQVIEW.GET_ADT_PAYLOAD(B.QUEUE_ID
, B.MSG_NUM
, "SYS"."AQ$_JMS_TEXT_MESSAGE"(NULL
, NULL
, NULL
, NULL)) USER_DATA
, CAST(NULL AS RAW(16)) PROPAGATED_MSGID
, B.SENDER_NAME SENDER_NAME
, B.SENDER_ADDRESS SENDER_ADDRESS
, B.SENDER_PROTOCOL SENDER_PROTOCOL
, B.DEQUEUE_MSGID ORIGINAL_MSGID
, CAST (NULL AS VARCHAR2(30)) ORIGINAL_QUEUE_NAME
, CAST(NULL AS VARCHAR2(30)) ORIGINAL_QUEUE_OWNER
, DECODE(B.STATE
, 12
, 'TIME_EXPIRATION'
,
NULL) EXPIRATION_REASON
, S.NAME CONSUMER_NAME
, S.ADDRESS ADDRESS
, S.PROTOCOL PROTOCOL
FROM SYS.QT245562_BUFFER B
, ALL_QUEUES Q
, "AQ$_WF_CONTROL_S" S
WHERE S.SUBSCRIBER_ID = B.SUBSCRIBER_ID
AND BITAND(S.SUBSCRIBER_TYPE
, 8) != 8
AND BITAND(B.STATE
, 4) = 4
AND Q.QID = B.QUEUE_ID UNION ALL SELECT P.Q_NAME QUEUE
, P.MSGID MSG_ID
, P.CORRID CORR_ID
, P.PRIORITY MSG_PRIORITY
, DECODE (TO_CHAR(B.STATE)
, '2'
, 'SPILLED'
,
'3'
, 'DEFERRED SPILLED'
,
'10'
, 'EXPIRED SPILLED'
,
NULL) MSG_STATE
, CAST(FROM_TZ(P.DELAY
, '-08:00')
AT TIME ZONE SESSIONTIMEZONE AS DATE) DELAY
, CAST(FROM_TZ(P.DELAY
, '-08:00')
AT TIME ZONE SESSIONTIMEZONE AS TIMESTAMP) DELAY_TIMESTAMP
, P.EXPIRATION EXPIRATION
, CAST(FROM_TZ(P.ENQ_TIME
, '-08:00')
AT TIME ZONE SESSIONTIMEZONE AS DATE) ENQ_TIME
, CAST(FROM_TZ(P.ENQ_TIME
, '-08:00')
AT TIME ZONE SESSIONTIMEZONE AS TIMESTAMP)
ENQ_TIMESTAMP
, P.ENQ_UID ENQ_USER_ID
, P.ENQ_TID ENQ_TXN_ID
, CAST(FROM_TZ(P.DEQ_TIME
, '-08:00')
AT TIME ZONE SESSIONTIMEZONE AS DATE) DEQ_TIME
, CAST(FROM_TZ(P.DEQ_TIME
, '-08:00')
AT TIME ZONE SESSIONTIMEZONE AS TIMESTAMP)
DEQ_TIMESTAMP
, P.DEQ_UID DEQ_USER_ID
, P.DEQ_TID DEQ_TXN_ID
, P.RETRY_COUNT RETRY_COUNT
, P.EXCEPTION_QSCHEMA EXCEPTION_QUEUE_OWNER
, P.EXCEPTION_QUEUE EXCEPTION_QUEUE
, P.USER_DATA USER_DATA
, CAST (NULL AS RAW(16))PROPAGATED_MSGID
, P.SENDER_NAME SENDER_NAME
, P.SENDER_ADDRESS SENDER_ADDRESS
, P.SENDER_PROTOCOL SENDER_PROTOCOL
, CAST (NULL AS RAW(16)) ORIGINAL_MSGID
, CAST (NULL AS VARCHAR2(30)) ORIGINAL_QUEUE_NAME
, CAST (NULL AS VARCHAR2(30)) ORIGINAL_QUEUE_OWNER
, DECODE(B.STATE
, 10
, 'TIME_EXPIRATION'
,
NULL) EXPIRATION_REASON
, S.NAME CONSUMER_NAME
, S.ADDRESS ADDRESS
, S.PROTOCOL PROTOCOL
FROM "AQ$_WF_CONTROL_P" P
, SYS.QT245562_BUFFER B
, "AQ$_WF_CONTROL_S" S
, ALL_QUEUES Q
WHERE B.SUBSCRIBER_ID = S.SUBSCRIBER_ID
AND BITAND(S.SUBSCRIBER_TYPE
, 8) != 8
AND BITAND(B.STATE
, 2) = 2
AND P.MSGID = B.MSGID
AND Q.QID = B.QUEUE_ID
AND P.Q_NAME = Q.NAME WITH READ ONLY
|
|
|