The Oracle B2B purge utilities are used as :
%. setenv.sh (b2b env)
% java -ms1024M -mx1024M oracle.tip.repos.purge.PurgeManager purgeRuntime -start 01-JAN-2007 -end 20-AUG-2007 STATUS
Execution context started.
Purge started, please wait...
Purge completed.
Or with PL/SQL
exec purge_runtime('01-JAN-2007',20-AUG-2007','STATUS
Where STATUS
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
I noticed Ramesh Nittur Anantharamaiah's blog today, nice of Ramesh to share his thought outside of the Oracle Forums.
Sphere: Related Content
