Is there a fast way to clear Exception queue from Oracle AQ?

Question

AQ$_MESSAGES_EXCEPTIONFirst of all I know there's this question: How to clear a queue in Oracle AQ but it doesn't have an answer.

I have a lot of messages(500k) in the exception queue in the Oracle AQ(I didn't know expired messages are moved to another queue so I didn't create a consumer for those). What I need now is to be able to delete those messages fast. I've read that it's not a good idea to clear the queue table via delete, because it could lead to inconsistent state. So I've put together following procedure, but it only clears about 50 messages/second

EXECUTE dbms_aqadm.start_queue(queue_name => 'AQ$_MESSAGES_EXCEPTION', 
                               enqueue => FALSE, dequeue => TRUE);

DECLARE
   dequeue_options     DBMS_AQ.dequeue_options_t;
   message_properties  DBMS_AQ.message_properties_t;
   message_handle      RAW(16);
   message             SYS.AQ$_JMS_MESSAGE;
   no_messages         EXCEPTION;
   pragma exception_init (no_messages, -25228);
BEGIN
   dequeue_options.wait := DBMS_AQ.NO_WAIT;
   dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE;
   LOOP
   DBMS_AQ.DEQUEUE(
      queue_name          =>     'AQ$_MESSAGES_EXCEPTION',
      dequeue_options     =>     dequeue_options,
      message_properties  =>     message_properties,
      payload             =>     message,
      msgid               =>     message_handle);
  DBMS_OUTPUT.put_line ('Message: ' || message_handle || ' dequeued');
  END LOOP;
  EXCEPTION
    WHEN no_messages THEN
      DBMS_OUTPUT.put_line (' ---- NO MORE MESSAGES  ---- ');
    WHEN others then
      DBMS_OUTPUT.put_line ('Exception queue not started for dequeue.');
END;

/

It seems really slow considering it's running on the database machine. This procedure takes about three hours with 500k messages. Can I do it in some more effective manner?

EDIT:

I tried the dequeue_array from the link here: http://www.oracle-developer.net/display.php?id=319 But I can't create the tables, so I'm trying to create an array to "store" the results. Here's what I've got:

DECLARE
    type messages_type  is varray(500) of SYS.AQ$_JMS_MESSAGE;
    messages            messages_type;
    dequeue_options     DBMS_AQ.dequeue_options_t;
    msg_properties      DBMS_AQ.message_properties_array_t;
    msg_ids             DBMS_AQ.MSGID_ARRAY_T;
    x_timeout           EXCEPTION;
    no_messages         EXCEPTION;
    dequeue_batch       PLS_INTEGER := 500;
    pragma exception_init (no_messages, -25228);
BEGIN        
    messages := messages_type();
    msg_properties := DBMS_AQ.MESSAGE_PROPERTIES_ARRAY_T();
    msg_properties.EXTEND(dequeue_batch);      
    msg_ids := DBMS_AQ.MSGID_ARRAY_T();   
    dequeue_options.wait := 5;   
LOOP
    DBMS_AQ.DEQUEUE_ARRAY(
    queue_name          =>     'AQ$_MESSAGES_EXCEPTION',
    dequeue_options     =>     dequeue_options,
    array_size          =>     dequeue_batch,
    message_properties_array  =>     msg_properties,
    payload_array             =>     messages,
    msgid_array               =>     msg_ids); 
...

I'm getting this error:

wrong number or types of arguments in call to 'DEQUEUE_ARRAY'

I think the problem is in the messages array, but I don't know what to do to make it work. Also, according to oracle doc(http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_aq.htm#i1000850), there should be another parameter:

error_array               OUT  error_array_t

But the explanation for this parameter is "Currently not implemented". What does it mean? Can it be left out? Should it be set to null? This is really confusing and google doesn't help here :(

score 3 · Answer 1 · answered Apr 22 '13 at 07:19

3

If you really want to dequeue, you could probably use the dequeue_array(n) function. That should be much faster.

but the link you provide does have the solution :

-- purge queue
DECLARE
 po_t dbms_aqadm.aq$_purge_options_t;
BEGIN
  dbms_aqadm.purge_queue_table('MY_QUEUE_TABLE', NULL, po_t);
END;

Otherwise, since this exception queue is created automatically, I guess you could just drop it, but I am not sure if that is safe

BEGIN
  DBMS_AQADM.STOP_QUEUE(
     queue_name => 'demo_queue'
     );
  DBMS_AQADM.DROP_QUEUE(
     queue_name => 'demo_queue'
     );
  DBMS_AQADM.DROP_QUEUE_TABLE(
     queue_table => 'demo_queue_table'
     );
END;

answered Apr 22 '13 at 07:19

Stéphane

11,755
7
49
63

But the problem is, that the exception queue doesn't have a separate table. So If I would use the solution from the link, I'd also be deleting data from the regular queue and I don't want that. I'll try the dequeue array and see if it's any faster. – NeplatnyUdaj Apr 22 '13 at 07:36
I give up. I'm not able to put together a procedure for calling dequeue_array. – NeplatnyUdaj Apr 22 '13 at 08:44
I see, I do this from .NET code, and I haven't encountered any problem. the Oracle library provides an API towards queues. Did you have a look at this : http://www.oracle-developer.net/display.php?id=319 – Stéphane Apr 22 '13 at 08:47
Staphane: I need to do this in the PL/SQL procedure and I don't have rights to create tables(which is needed in the examples in the link). I'm updating the question with my attempt – NeplatnyUdaj Apr 22 '13 at 08:51

ivanzg · Answer 2 · 2016-05-10T12:10:42.790

You can use procedure "purge_queue_table" (as @Stephane said) from dbms_aqadm package but with "purge_condition" parameter specified , with this parameter you can select which messages you delete :

Example :

declare

    purge_options dbms_aqadm.aq$_purge_options_t;

begin

      purge_options.block := false;

      purge_options.delivery_mode := dbms_aqadm.persistent;

      dbms_aqadm.purge_queue_table
         (queue_table     => 'vista.svig_std_tab',
          purge_condition => 'qtview.queue = ''AQ$_SVIG_STD_TAB_E'' and qtview.enq_time < to_date(''01.06.2014 00:00:00'',''dd.mm.yyyy hh24:mi:ss'') ',
          purge_options   => purge_options
         );

end;
/

This example deletes messages that are from the specified exception queue and are older than the specified date. It also does this in a much quicker fashion than using "dbms_aq.dequeue" procedure.

Is there a fast way to clear Exception queue from Oracle AQ?

2 Answers2