Whether or not your Oracle Advanced Queue (AQ) will enqueue and dequeue messages can be determined with a simple query:
1 2 3 4 5 6 7 8 | SELECT name , queue_table , enqueue_enabled , dequeue_enabled , max_retries , retry_delay FROM user_queues ; |
The query example is based on user_queues and in that case it has to be performed as the owner of the queue. The result wil be something like:
AQ enqueu dequeue
As a quick follow up on the previous post… If you are using a JMS queue with Oracle´s Advanced Queueing or Streams (as they have been extended to) the previously described tool can´t help you out.
For this case a blog by a former colleague helps us out. This approach boils down to…
1 2 3 4 5 | SELECT owner , queue_table , TYPE , user_comment FROM all_queue_tables |
1 2 3 4 5 6 | SELECT msgid , enq_time , enq_uid , qt.user_data.text_vc FROM my_queue_table qt --alter the table name WHERE q_name = 'MY_QUEUE' --alter the queue name |
Async queue
One of our customers asked me exactly this question. They have an Oracle ESB and it seemed that it could not handle all incoming requests. With the queues being a decoupling point between the incoming requests and the related transactions to the back office systems, its size can be an indication of just how busy the system is.
As I couldn’t find the answer using the Oracle Enterprise Manager Oracle Enterprise Manager and its MBeans, I started to look on the web for a tool. The tool I found and started to use was HermesJMS. This helps you interact with JMS providers making it easy to browse or search queues and topics, copy messages around and delete them.
HermesJMS screenshot
The HermesJMS requires Java 1.6. It has a clear installation guide installation guide and it is tested for multiple JMS providers. Besides the guide we needed for the Oracle AS there is also another blog describing the install and use of HermesJMS.
Using HermesJMS we were able to answer this customers question.
