3

We have a messaging system based on Oracle AQ - it works very well, enqueing and dequeing without any problems.

Now we got a request to add some sanity checks before startup and during runtime, for example "check if the queue actually exists for the supplied db-user" and "periodically check the amount of messages in the queue".

The latter seems reasonably easy to solve, look up queuetable, count number of messages, but the first weirds me out. Going through the documentation and the available methods from DatabaseMetaData, I do not see a way to actually check this without trying to enqueue/dequeue. The database tells me everything about tables, keys, schemas and so on, but I cannot for the life of me find queues. Weirdly, I also cannot find the queuetables, although these might simply not be a "table" in the classical way.

Am I missing something? Is the information unavailable or in another place?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Scorpio
  • 2,309
  • 1
  • 27
  • 45

2 Answers2

2

To check if a queue exists for your user, I advise you check the USER_QUEUES table:

SELECT * FROM USER_QUEUES
 WHERE name  = '<that_queue>'

For another user whose name you know (with DBA rights):

SELECT * FROM DBA_QUEUES
 WHERE owner = '<that_user_name>'
   AND name  = '<that_queue>'
J. Chomel
  • 8,193
  • 15
  • 41
  • 69
1

Try this query:

SELECT name, queue_type, waiting, ready, expired
FROM user_queues q
JOIN v$aq v ON q.qid = v.qid
WHERE q.name = 'queue_name'

The tables user_queues, all_queues and dba_queues contain information about queues.

The view v$aq contains statistics about queues (statistics are not refreshed in real-time).

Crayons
  • 1,906
  • 1
  • 14
  • 35
Arkadiusz Łukasiewicz
  • 6,241
  • 1
  • 11
  • 17
  • 1
    This is normally good enough. However, note that in some cases the statistics in v$aq can be plain wrong. The queue depth can be listed as negative, for example, even though there is data to read. The safest approach is to use select count on the queue table filtering on status. – ewramner May 05 '17 at 12:26