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
DECLARE
po_t dbms_aqadm.aq$_purge_options_t;
BEGIN
dbms_aqadm.purge_queue_table('SCOTT.JMS_QUEUE_TABLE',
purge_condition => null,
purge_options => po_t);
END;
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 🙂
Recent Comments