CODEWITHSUNDEEP
oracle-databasedatabase-metadataoracle-aqadvanced-queuing
Scorpio
Scorpio

Reputation: 2327

Programmatically check if Oracle AQ Queue exists

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?

Upvotes: 3

Views: 10878

Answers (2)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

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).

Upvotes: 1

J. Chomel
J. Chomel

Reputation: 8395

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

Upvotes: 3

Related Questions