Oracle Advanced Queuing - Subscriber Message Tracking

Question

How does Oracle Advanced Queuing track which messages that a subscriber has dequeued for a multiconsumer queue?

The USER_QUEUE_SUBSCRIBERS table tracks the subscribers per queue. I can't find a table/view though that tracks the state of the subscriber for a given queue (e.g. what was the last message that the subscriber dequeued?).

Answer 1

Query the AQ$<your_queue_table>.MSG_STATE. The AQ$<your_queue_table>.MSG_STATE has an entry for each subscriber for each message.

select queue, consumer_name, deq_txn_id, deq_time, deq_user_id, 
 user_data, msg_state
  from aq$MC_QUEUE_TABLE
  where queue = 'MC_QUEUE';

In example my queue table is MC_QUEUE_TABLE and my queue is MC_QUEUE.

The MSG_STATE takes:

• READY - The message is ready to be processed, i.e., either the delay
• RETAINED or PROCESSED - The message has been successfully processed (dequeued) but will remain in the queue until the retention_time specified for the queue while executing dbms_aqadm.create_queue has been reached.
• etc.

Let's assume that we have 2 messages (msg1, msg2) and 2 subscribers (sub1, sub2). And sub1 has already dequeued the msg1. As the result we will see:

QUEUE                          USER_DATA MSG_STATE        CONSUMER_NAME                 
MC_QUEUE                       (msg1)    READY            SUB2          
MC_QUEUE                       (msg2)    READY            SUB2          
MC_QUEUE                       (msg1)    PROCESSED        SUB1           
MC_QUEUE                       (msg2)    READY            SUB1

Because of retention_time, you may not be able to see your processed messages.