3 Replies Latest reply: Jul 3, 2013 8:09 AM by Greg Spall RSS

    Re enable dequeueing for expired messages in AQ

    929255

      This is copied from SQL and PL/SQLDiscussions

      I tried to perform 'Move' but I must be doing sth wrong.

       

      Dear all,

       

      I need to reprocess expired messages in an advanced queue.

      Is there a script that allows either to retry dequeuing or to re insert/enqueue again the messages?

       

      The creation script of the queue follows:

      1. BEGIN 
      2.   SYS.DBMS_AQADM.CREATE_QUEUE 
      3.   ( 
      4.     QUEUE_NAME          =>   'CC.PMTS' 
      5.    ,QUEUE_TABLE         =>   'CC.PMTS_DATA' 
      6.    ,QUEUE_TYPE          =>   SYS.DBMS_AQADM.NORMAL_QUEUE 
      7.    ,MAX_RETRIES         =>   0 
      8.    ,RETRY_DELAY         =>   0 
      9.    ,RETENTION_TIME      =>   0 
      10.    ,COMMENT             =>   'Comment' 
      11.    ); 
      12. END; 

      Thanks

        • 1. Re: Re enable dequeueing for expired messages in AQ
          Greg Spall

          Well, yes, that's what the MAX_RETRIES and RETRY_DELAY does:

           

          BEGIN 
            SYS.DBMS_AQADM.CREATE_QUEUE 
            ( 
              QUEUE_NAME          =>   'CC.PMTS' 
             ,QUEUE_TABLE         =>   'CC.PMTS_DATA' 
             ,QUEUE_TYPE          =>   SYS.DBMS_AQADM.NORMAL_QUEUE 
             ,MAX_RETRIES         =>   6 
             ,RETRY_DELAY         =>   10 
             ,RETENTION_TIME      =>   0 
             ,COMMENT             =>   'Comment' 
             ); 
          END; 
          

           

          There, that will retry the item 6 times, with a 10s delay between each retry. (Note: I haven't verified the time, but if memory serves correctly, it is by seconds)

          • 2. Re: Re enable dequeueing for expired messages in AQ
            929255

            My problem is that the messages are expired and I want to reprocess them. Not how to configure the queue.

            Thank you

            • 3. Re: Re enable dequeueing for expired messages in AQ
              Greg Spall

              929255 wrote:

               

              My problem is that the messages are expired and I want to reprocess them. Not how to configure the queue.

              Thank you

               

              Oh, then just go fetch it from the expired queue, and re-queue it. I don't think there's a given script just :

               

              1) retrieve record/item from expired queue.

              2) re-queue it to your main queue.