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 :(

Answer 1

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;