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 |
For more information about Oracle Advanced Queuing (AQ), please refer to Database Advanced Queuing User’s Guide