PROCESSED Messages not clearing from Oracle Queue

I was contacted by Dev team to look into Development database where Oracle queue size kept  on increasing. As per them messages were not getting cleared.They were running count on Queue table and the count kept increasing.
To find out exact cause I included the msg_state in the query and found out that there were lot of unprocessed messages

SQL> select queue,msg_state,count(*) from A$$JMS_QUEUE_TABLE group by queue,msg_state;

QUEUE                   MSG_STATE      COUNT(*)
------------------------------ ---------------- ----------
AQ$_JMS_QUEUE_TABLE_E      EXPIRED             3
JMS_QUEUE               PROCESSED           343
JMS_QUEUE               READY             3

Since this was dev box,I initially tried purging the queue using dbms_aqadm.purge_queue_table

purge_condition => null,
purge_options => po_t);

This removed the EXPIRED and READY messages but PROCESSED messages count didn't decrease.
Next  I checked queue definition using all_queues.I saw a value named retention specified to 3600.

SQL> SELECT owner, name, retention FROM all_queues WHERE name LIKE '%JMS%';

OWNER                   NAME                  RETENTION
-------            ------------------        --------------
SCOTT               JMS_QUEUE              3600
SCOTT               AQ$_JMS_QUEUE_TABLE_E      0

When a queue is defined in Oracle, you can define how long a message can remain visible once it has been dequeued (i.e. the retention period). Modifying the retention to 0 cleared the messages instantly

exec dbms_aqadm.alter_queue(QUEUE_NAME=>'SCOTT.JMS_QUEUE',RETENTION_TIME=>0)

Some interesting learning for me 🙂

Leave a Reply