B2B and Tracking Messages in AQ

This thread on Tracking Messages in AQ in the Oracle forums is intresting

Ramesh Nittur Anantharamaiah suggests the following to for a status overview:
SELECT a.queue_table, a.name, b.waiting, b.ready, b.expired
FROM dba_queues a, v$aq b
WHERE a.qid = b.qidAND owner = 'B2B'

Martien van den Akker suggests the following query to be able to see the message ids easily:

SELECT qtb.queue,
qtb.msg_id,
qtb.msg_state,
qtb.enq_time,
qtb.delay,
qtb.user_data.msg_id,
qtb.user_data.inreplyto_msg_id b2b_inreplyto_msg_id,
decode(instr(qtb.user_data.inreplyto_msg_id, ':'), 0, qtb.user_data.inreplyto_msg_id, SUBSTR(qtb.user_data.inreplyto_msg_id, 1, instr(qtb.user_data.inreplyto_msg_id, ':') -1)) inreplyto_msg_id,
decode(instr(qtb.user_data.inreplyto_msg_id, ':'), 0, NULL, SUBSTR(qtb.user_data.inreplyto_msg_id, instr(qtb.user_data.inreplyto_msg_id, ':') + 1)) conversation_id,
qtb.user_data.from_party,
qtb.user_data.to_party,
qtb.user_data.action_name,
qtb.user_data.doctype_name,
qtb.user_data.doctype_revision,
qtb.user_data.msg_type,
qtb.user_data.payload,
qtb.user_data.attachment,
qtb.consumer_namefrom aq$ip_qtab qtb
ORDER BY enq_time DESC;

I also noted that if you have GridControl 10.2 you can create a UDM based on the following query (reference to IOUG Collaborate 07: Oracle 10g Grid Control ) :
select q.owner '.'q.name queue_name, s.readyfrom v$aq s, dba_queues q where s.qid = q.qidand q.owner IN ('B2B')

And then set alert levels as you wish:
Warning "B2B.IP_OUT_QUEUE:50;B2B.IP_IN_QUEUE:50"
Error "B2B.IP_OUT_QUEUE:100;B2B.IP_IN_QUEUE:100"

And then create create error messages such as "Queue %key% contains %value% unattended messages."

Today I also use this query to have a peek in the actual messages and "DUMP" the contents to be able to try to understand a charachter set issue:

SELECT qtb.queue,
qtb.msg_id,
DBMS_LOB.SUBSTR(qtb.user_data.payload,4000,1),
DUMP (DBMS_LOB.SUBSTR(qtb.user_data.payload,4000,1),16)
FROM aq$ip_qtab qtb
WHERE MSG_ID = '3989F49C24206660E0440003BAE85389';

To list the consumers registrated for the B2B in and out queues one may use the following SQL:

SELECT *
FROM aq$ip_qtab_s

Sphere: Related Content