What are possible message states in Oracle Advanced Queuing (AQ) ?

When we experience any problems with Oracle AQ (Advanced Queuing), messages are not going in or out, first place to look at is queue table itself. There is column STATE. It can tell us if a message is stuck for some reason or in any other state which can give us important information for debugging. Here are all states a message can hold:

Value Name Meaning
0 READY The message is ready to be processed, i.e., either the delay
time of the message has passed or the message did not have
a delay time specified
1 WAITING or WAIT The delay specified by message_properties_t.delay while
executing dbms_aq.enqueue has not been reached.
2 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.
3 EXPIRED The message was not successfully processed (dequeued) in
either 1) the time specified by message_properties_t.expiration
while executing dbms_aq.enqueue or 2) the maximum number of
dequeue attempts (max_retries) specified for the queue while
executing dbms_aqadm.create_queue.
4 IN MEMORY User-enqueued buffered message
7 SPILLED User-enqueued buffered message spilled to disk
8 DEFERRED Buffered messages enqueued by a Streams Capture process
9 DEFERRED SPILLED Capture-enqueued buffered messages that have been spilled to disk
10 BUFFERED EXPIRED User-enqueued expired buffered messages

This information is pulled out from “My Oracle Support” knowledge base, document ID 102330.1

For more information about Oracle Advanced Queuing (AQ), please refer to Database Advanced Queuing User’s Guide


About Danas Tarnauskas

Leave a Reply

Your email address will not be published.