4 Replies Latest reply: Mar 18, 2014 6:27 AM by 976208 RSS

    Why message expired in AQ

    976208

      Hi Experts,

       

       

      In AQ we have some expired messages. I have scene in the queue table.

       

       

      EXPIRATION_REASON-- MAX_RETRY_EXCEEDED

      RETRY_COUNT--1000

       

       

      What is the reason for trying 1000 times to dequeue the message?

      Where and how to see that expired message content?

      Can we reprocess that expired message?

       

       

       

       

      Please help me.

       

       

      Thanks.

        • 1. Re: Why message expired in AQ
          WGabriel

          Hello,

           

          >What is the reason for trying 1000 times to dequeue the message?

           

          During the create of an AQ (DBMS_AQADM.CREATE_QUEUE) you obviously specified

          MAX_RETRIES => 1000. So there will be 1000 retries for the dequeue process to

          dequeue a message. If there will be no success this AQ record will be expired

          and copied to the exception queue.

           

          >Where and how to see that expired message content?

           

          You can find these expired messages (MSG_STATE = 'EXPIRED') in your

          AQ exception queues (AQ$<queue name>_E). The message content depends on your AQ payload type

          (VARCHAR2, BLOB etc.) in column USER_DATA.

           

          select * from dba_queues

          where queue_type = 'EXCEPTION_QUEUE';

           

          >Can we reprocess that expired message?

           

          You cannot specify, that you would like to enqueue again all expired messages.

          Instead you have to enqueue these data (using the data in your exception queue), again.

           

          Kind regards,

          WoG

          • 2. Re: Why message expired in AQ
            976208

            Hi,

             

             

            Yes we have set MAX_RETRIES => 1000

            It will be 1000 retries for the dequeue process to dequeue a message. If there will be no success this AQ record will be expired.

            Thats fine.

             

             

            My question was why it tried 1000 to dequeue the message why can't it would be done in 1 or 2 tries?

             

             

            SELECT * FROM GSD_QUEUE MSG_STATE = 'EXPIRED';

             

             

            The USER_DATA column is showing NULL.How to see the message content.

             

             

            select * from dba_queues

            where queue_type = 'EXCEPTION_QUEUE';

             

             

            I got the exception queue name

             

             

            SELECT * FROM AQ$_GSD_QUEUE_E;

             

             

            It's not working.

             

             

            Please help me.

             

             

            Thanks.

            • 3. Re: Why message expired in AQ
              WGabriel

              Hello,

               

              did you check your enqueue process concerning the payload content in column USER_DATA ?

               

              >My question was why it tried 1000 to dequeue the message why can't it would be done in 1 or 2 tries?

              Of course, this should be done in one try.

              Check first if your queue is enabled for enqueing / dequeing:

               

              select dequeue_enabled

              from dba_queues

              where name = 'GSD_QUEUE';

               

              This value should be 'YES'.

               

              Kind regards,

              WoG

              • 4. Re: Why message expired in AQ
                976208

                Hi ,

                 

                 

                SELECT * FROM GSD_QUEUE MSG_STATE = 'EXPIRED';

                 

                The USER_DATA column is showing NULL.How to see the message content.

                 

                 

                SELECT * FROM GSD_QUEUE MSG_STATE = 'READY';

                 

                The USER_DATA column is showing data.

                 

                 

                select dequeue_enabled

                from dba_queues

                where name = 'GSD_QUEUE';

                 

                I got the output 'YES'.

                 

                 

                Please help me.

                 

                 

                Thanks.