VIEW: SYS.ALL_QUEUES

Source

View Text - Preformatted

select u.name OWNER, q.name NAME, t.name QUEUE_TABLE, q.eventid QID,
       decode(q.usage, 1, 'EXCEPTION_QUEUE', 2, 'NON_PERSISTENT_QUEUE',
              'NORMAL_QUEUE') QUEUE_TYPE,
       q.max_retries MAX_RETRIES, q.retry_delay RETRY_DELAY,
       decode(bitand(q.enable_flag, 1), 1 , '  YES  ', '  NO  ')ENQUEUE_ENABLED,
       decode(bitand(q.enable_flag, 2), 2 , '  YES  ', '  NO  ')DEQUEUE_ENABLED,
       decode(q.ret_time, -1, ' FOREVER', q.ret_time) RETENTION,
       substr(q.queue_comment, 1, 50) USER_COMMENT,
       s.network_name NETWORK_NAME,
       decode(q.sharded, 1, 'TRUE', 'FALSE') SHARDED
from system.aq$_queues q, system.aq$_queue_tables t, sys.user$ u, sys.obj$ ro,
dba_services s
where u.name  = t.schema
and   q.table_objno = t.objno
and   ro.owner# = u.user#
and   ro.obj# = q.eventid
and  (ro.owner# = userenv('SCHEMAID')
      or ro.obj# in
           (select oa.obj#
            from sys.objauth$ oa
            where grantee# in (select kzsrorol from x$kzsro))
      or exists (select null from v$enabledprivs
                 where priv_number in (-218 /* MANAGE ANY QUEUE */,
                                       -219 /* ENQUEUE ANY QUEUE */,
                                       -220 /* DEQUEUE ANY QUEUE */))
      or ro.obj# in
           (select q.eventid from system.aq$_queues q,
                                  system.aq$_queue_tables t
              where q.table_objno = t.objno
              and bitand(t.flags, 8) = 0
              and exists (select null from sys.objauth$ oa, sys.obj$ o
                          where oa.obj# = o.obj#
                          and (o.name = 'DBMS_AQ' or o.name = 'DBMS_AQADM')
                          and o.owner# = 0
                          and o.type# = 9
                          and oa.grantee# = userenv('SCHEMAID')))
     )
and   q.service_name = s.name (+)

View Text - HTML Formatted

SELECT U.NAME OWNER
, Q.NAME NAME
, T.NAME QUEUE_TABLE
, Q.EVENTID QID
, DECODE(Q.USAGE
, 1
, 'EXCEPTION_QUEUE'
, 2
, 'NON_PERSISTENT_QUEUE'
, 'NORMAL_QUEUE') QUEUE_TYPE
, Q.MAX_RETRIES MAX_RETRIES
, Q.RETRY_DELAY RETRY_DELAY
, DECODE(BITAND(Q.ENABLE_FLAG
, 1)
, 1
, ' YES '
, ' NO ')ENQUEUE_ENABLED
, DECODE(BITAND(Q.ENABLE_FLAG
, 2)
, 2
, ' YES '
, ' NO ')DEQUEUE_ENABLED
, DECODE(Q.RET_TIME
, -1
, ' FOREVER'
, Q.RET_TIME) RETENTION
, SUBSTR(Q.QUEUE_COMMENT
, 1
, 50) USER_COMMENT
, S.NETWORK_NAME NETWORK_NAME
, DECODE(Q.SHARDED
, 1
, 'TRUE'
, 'FALSE') SHARDED FROM SYSTEM.AQ$_QUEUES Q
, SYSTEM.AQ$_QUEUE_TABLES T
, SYS.USER$ U
, SYS.OBJ$ RO
, DBA_SERVICES S WHERE U.NAME = T.SCHEMA AND Q.TABLE_OBJNO = T.OBJNO AND RO.OWNER# = U.USER# AND RO.OBJ# = Q.EVENTID AND (RO.OWNER# = USERENV('SCHEMAID') OR RO.OBJ# IN (SELECT OA.OBJ#
FROM SYS.OBJAUTH$ OA
WHERE GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)) OR EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-218 /* MANAGE ANY QUEUE */
, -219 /* ENQUEUE ANY QUEUE */
, -220 /* DEQUEUE ANY QUEUE */)) OR RO.OBJ# IN (SELECT Q.EVENTID
FROM SYSTEM.AQ$_QUEUES Q
, SYSTEM.AQ$_QUEUE_TABLES T
WHERE Q.TABLE_OBJNO = T.OBJNO
AND BITAND(T.FLAGS
, 8) = 0
AND EXISTS (SELECT NULL
FROM SYS.OBJAUTH$ OA
, SYS.OBJ$ O
WHERE OA.OBJ# = O.OBJ#
AND (O.NAME = 'DBMS_AQ' OR O.NAME = 'DBMS_AQADM')
AND O.OWNER# = 0
AND O.TYPE# = 9
AND OA.GRANTEE# = USERENV('SCHEMAID'))) ) AND Q.SERVICE_NAME = S.NAME (+)