Programmatically check if Oracle AQ Queue exists

Question

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?

Answer 1

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>'