Unable to View Queue Statistics

In TOAD for Oracle, I cannot see our AQ's in Schema Browser. Can you tell me what privs are needed in production so I can pass it along to our DBA's? Non-prod instances are fine. For example, here's what I see in a test instance:

What do you see on production?

No queues listed at all, or listed, but a <no statistics available>' message on the right hand side?

If the latter, you need the SELECT or READ privilege on V$AQ

Hi John,
The former, no queues at all

HI David,

Looking at the code, it seems like you should be getting an Oracle error if you are lacking privileges on one of the data dictionary views. You can turn on spool SQL to double-check that. Main menu -> Database -> Spool SQL -> Spool to screen. Then load the queues and you'll see that SQL that Toad is running to load them.

My guess is that the queues are in another schema, and Toad is using the ALL_ views instead of DBA_ views (because you don't have privs on the DBA views). So you can either get SELECT on the DBA_ views that Toad is querying here, or the SELECT ANY DICTIONARY privilege. Or get the ENQUEUE/DEQUEUE privleges on the queues in question, so they will show up in the ALL_ views.

Oh, and if you are granted SELECT on the DBA views (or get the SELECT ANY DICTIONARY priv), you'll need to disconnect/reconnect for Toad to notice. I don't think you'll need to reconnect if you are granted ENQUEUE/DEQUEUE on the individual queues.


1 Like