SELECT q_name QUEUE, msgid MSG_ID, corrid CORR_ID, priority MSG_PRIORITY, decode(state, 0, 'READY',
1, 'WAIT',
2, 'PROCESSED',
3, 'EXPIRED',
10, 'BUFFERED_EXPIRED') MSG_STATE, cast(FROM_TZ(delay, '-08:00')
at time zone sessiontimezone as date) DELAY, delay DELAY_TIMESTAMP, expiration, cast(FROM_TZ(enq_time, '-08:00')
at time zone sessiontimezone as date) ENQ_TIME, cast(FROM_TZ(enq_time, '-08:00')
at time zone sessiontimezone as timestamp)
ENQ_TIMESTAMP, enq_uid ENQ_USER_ID, enq_tid ENQ_TXN_ID, cast(FROM_TZ(deq_time, '-08:00')
at time zone sessiontimezone as date) DEQ_TIME, cast(FROM_TZ(deq_time, '-08:00')
at time zone sessiontimezone as timestamp)
DEQ_TIMESTAMP, deq_uid DEQ_USER_ID, deq_tid DEQ_TXN_ID, 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, decode (state, 3,
decode (deq_tid, 'INVALID_TRANSACTION', NULL,
exception_queue), NULL)
ORIGINAL_QUEUE_NAME, decode (state, 3,
decode (deq_tid, 'INVALID_TRANSACTION', NULL,
exception_qschema), NULL)
ORIGINAL_QUEUE_OWNER, decode(state, 3,
decode(deq_time, NULL,
decode(deq_tid, NULL,
decode (expiration , NULL , 'MAX_RETRY_EXCEEDED',
'TIME_EXPIRATION'),
'INVALID_TRANSACTION', NULL,
'MAX_RETRY_EXCEEDED'), NULL), NULL)
EXPIRATION_REASON , sender_name SENDER_NAME, sender_address SENDER_ADDRESS, sender_protocol SENDER_PROTOCOL, dequeue_msgid ORIGINAL_MSGID FROM "ECX_IN_OAG_Q_TABLE" WHERE state != 7 AND state != 9 WITH READ ONLY
SELECT Q_NAME QUEUE
, MSGID MSG_ID
, CORRID CORR_ID
, PRIORITY MSG_PRIORITY
, DECODE(STATE
, 0
, 'READY'
,
1
, 'WAIT'
,
2
, 'PROCESSED'
,
3
, 'EXPIRED'
,
10
, 'BUFFERED_EXPIRED') MSG_STATE
, CAST(FROM_TZ(DELAY
, '-08:00')
AT TIME ZONE SESSIONTIMEZONE AS DATE) DELAY
, DELAY DELAY_TIMESTAMP
, EXPIRATION
, CAST(FROM_TZ(ENQ_TIME
, '-08:00')
AT TIME ZONE SESSIONTIMEZONE AS DATE) ENQ_TIME
, CAST(FROM_TZ(ENQ_TIME
, '-08:00')
AT TIME ZONE SESSIONTIMEZONE AS TIMESTAMP)
ENQ_TIMESTAMP
, ENQ_UID ENQ_USER_ID
, ENQ_TID ENQ_TXN_ID
, CAST(FROM_TZ(DEQ_TIME
, '-08:00')
AT TIME ZONE SESSIONTIMEZONE AS DATE) DEQ_TIME
, CAST(FROM_TZ(DEQ_TIME
, '-08:00')
AT TIME ZONE SESSIONTIMEZONE AS TIMESTAMP)
DEQ_TIMESTAMP
, DEQ_UID DEQ_USER_ID
, DEQ_TID DEQ_TXN_ID
, 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
, DECODE (STATE
, 3
,
DECODE (DEQ_TID
, 'INVALID_TRANSACTION'
, NULL
,
EXCEPTION_QUEUE)
, NULL)
ORIGINAL_QUEUE_NAME
, DECODE (STATE
, 3
,
DECODE (DEQ_TID
, 'INVALID_TRANSACTION'
, NULL
,
EXCEPTION_QSCHEMA)
, NULL)
ORIGINAL_QUEUE_OWNER
, DECODE(STATE
, 3
,
DECODE(DEQ_TIME
, NULL
,
DECODE(DEQ_TID
, NULL
,
DECODE (EXPIRATION
, NULL
, 'MAX_RETRY_EXCEEDED'
,
'TIME_EXPIRATION')
,
'INVALID_TRANSACTION'
, NULL
,
'MAX_RETRY_EXCEEDED')
, NULL)
, NULL)
EXPIRATION_REASON
, SENDER_NAME SENDER_NAME
, SENDER_ADDRESS SENDER_ADDRESS
, SENDER_PROTOCOL SENDER_PROTOCOL
, DEQUEUE_MSGID ORIGINAL_MSGID
FROM "ECX_IN_OAG_Q_TABLE"
WHERE STATE != 7
AND STATE != 9 WITH READ ONLY
|
|
|