Solved

Oracle AQ Queue clogged with expired messages

Posted on 2009-05-13
4
3,351 Views
Last Modified: 2013-12-07
I have an application that uses Oracle AQ. We have a queue that has accumulated more than 5 million expired messages. One option is to drop and create the queue again but it seems to take a very long time. Can some one suggest alternate options?
0
Comment
Question by:rjha
  • 2
4 Comments
 
LVL 13

Expert Comment

by:anand_2000v
ID: 24386865
Dequeue from Exception queue

Exception Queues

An exception queue is a repository for expired or unserviceable messages. Applications cannot directly enqueue into exception queues. Also, a multiconsumer exception queue cannot have subscribers associated with it. However, an application that intends to handle these expired or unserviceable messages can dequeue them exactly once from the exception queue using remove mode. The consumer name specified while dequeuing should be null. Messages can also be dequeued from the exception queue by specifying the message identifier.

Refer:http://download.oracle.com/docs/cd/B19306_01/server.102/b14257/aq_intro.htm
0
 

Accepted Solution

by:
rjha earned 0 total points
ID: 24571501
Hi,

I have been able to get a solution for this issue. I am posting the details below:

1. 'START' the exception queue and allow dequeueing on it
BEGIN
  SYS.DBMS_AQADM.START_QUEUE
  (
    QUEUE_NAME => 'exception queue name'
   ,ENQUEUE => FALSE
   ,DEQUEUE => TRUE
   );
END;
/

2. Then use a PL/SQL block like one below to read expired messages from the exception queue:

DECLARE
  l_dequeue_options     DBMS_AQ.dequeue_options_t;
  l_message_properties  DBMS_AQ.message_properties_t;
  l_message_handle      RAW(16);
  l_event_msg           <event payload type>;
  cursor c_exp_mesg is
  select MSG_ID
  from <queue table name>
  where msg_state = 'EXPIRED';
BEGIN
  l_dequeue_options.consumer_name := null;
  for rec in c_exp_mesg
  LOOP
      l_dequeue_options.msgid  := rec.msg_id;
      DBMS_AQ.dequeue(queue_name          => 'exception queue name',
                      dequeue_options     => l_dequeue_options,
                      message_properties  => l_message_properties,
                      payload             => l_event_msg,
                      msgid               => l_message_handle);
    -- process your message here in whatever way you want..

END;
/
0
 

Author Comment

by:rjha
ID: 24571534
Hi,

I have posted the solution that worked for me. Please do not delete this question but you can mark the solution provided as the accepted solution as it worked for me and can be helpful for others facing a similar issue.

Thanks,
Raj
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
If your app took Google’s lash recently, here are the 5 most likely reasons.
Viewers will learn how to use the Hootsuite Dashboard.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now