VIEW: SYS.ALL_QUEUE_SUBSCRIBERS

Source

View Text - Preformatted

select su.owner OWNER, su.queue_name QUEUE_NAME, su.queue_table QUEUE_TABLE,
       su.consumer_name CONSUMER_NAME, su.address ADDRESS,
       su.protocol PROTOCOL, su.transformation TRANSFORMATION,
       r.rule_condition RULE,
       decode(bitand(su.sub_type, 192), 64, 'PERSISTENT',
                                         128, 'BUFFERED',
                                         192, 'PERSISTENT_OR_BUFFERED',
                                         'NONE') DELIVERY_MODE,
       decode(bitand(su.sub_type, 32960), 32960, 'YES',
                                       'NO') IF_NONDURABLE_SUBSCRIBER,
       decode(bitand(su.sub_type, 512), 512, 'TRUE', 'FALSE') QUEUE_TO_QUEUE,
       su.subscriber_id SUBSCRIBER_ID,
       su.pos_bitmap POS_BITMAP
FROM   ( select  u.name OWNER, q.name QUEUE_NAME, t.name QUEUE_TABLE,
                 s.name CONSUMER_NAME, s.address ADDRESS, s.protocol PROTOCOL,
                 s.trans_name TRANSFORMATION, s.sub_type SUB_TYPE,
                 s.rule_name RULE_NAME, s.subscriber_id SUBSCRIBER_ID,
                 s.pos_bitmap POS_BITMAP
         FROM    system.aq$_queues q, system.aq$_queue_tables t, sys.user$ u,
                 sys.obj$ ro, sys.user$ cu,
                 TABLE(aq$_get_subscribers(u.name, q.name, t.name,
                                           cu.name, q.eventid, t.flags)) s
         where u.name  = t.schema
         and   q.table_objno = t.objno
         and   bitand(t.flags, 1) = 1 and q.usage!=1
         and   ro.owner# = u.user#
         and   ro.obj# = q.eventid
         and   cu.user# = userenv('SCHEMAID')
         and  (ro.owner# = userenv('SCHEMAID')
               or ro.obj# in
                    (select oa.obj#
                     from sys.objauth$ oa
                     where oa.privilege# in (21, 41) and
                           grantee# in (select kzsrorol from x$kzsro))
               or exists (select null from v$enabledprivs
                          where priv_number = -220)
               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')))
              )
       ) su, sys.dba_rules r
where su.rule_name = r.rule_name(+)
and   su.owner = r.rule_owner(+)

View Text - HTML Formatted

SELECT SU.OWNER OWNER
, SU.QUEUE_NAME QUEUE_NAME
, SU.QUEUE_TABLE QUEUE_TABLE
, SU.CONSUMER_NAME CONSUMER_NAME
, SU.ADDRESS ADDRESS
, SU.PROTOCOL PROTOCOL
, SU.TRANSFORMATION TRANSFORMATION
, R.RULE_CONDITION RULE
, DECODE(BITAND(SU.SUB_TYPE
, 192)
, 64
, 'PERSISTENT'
, 128
, 'BUFFERED'
, 192
, 'PERSISTENT_OR_BUFFERED'
, 'NONE') DELIVERY_MODE
, DECODE(BITAND(SU.SUB_TYPE
, 32960)
, 32960
, 'YES'
, 'NO') IF_NONDURABLE_SUBSCRIBER
, DECODE(BITAND(SU.SUB_TYPE
, 512)
, 512
, 'TRUE'
, 'FALSE') QUEUE_TO_QUEUE
, SU.SUBSCRIBER_ID SUBSCRIBER_ID
, SU.POS_BITMAP POS_BITMAP FROM ( SELECT U.NAME OWNER
, Q.NAME QUEUE_NAME
, T.NAME QUEUE_TABLE
, S.NAME CONSUMER_NAME
, S.ADDRESS ADDRESS
, S.PROTOCOL PROTOCOL
, S.TRANS_NAME TRANSFORMATION
, S.SUB_TYPE SUB_TYPE
, S.RULE_NAME RULE_NAME
, S.SUBSCRIBER_ID SUBSCRIBER_ID
, S.POS_BITMAP POS_BITMAP
FROM SYSTEM.AQ$_QUEUES Q
, SYSTEM.AQ$_QUEUE_TABLES T
, SYS.USER$ U
, SYS.OBJ$ RO
, SYS.USER$ CU
, TABLE(AQ$_GET_SUBSCRIBERS(U.NAME
, Q.NAME
, T.NAME
, CU.NAME
, Q.EVENTID
, T.FLAGS)) S
WHERE U.NAME = T.SCHEMA
AND Q.TABLE_OBJNO = T.OBJNO
AND BITAND(T.FLAGS
, 1) = 1
AND Q.USAGE!=1
AND RO.OWNER# = U.USER#
AND RO.OBJ# = Q.EVENTID
AND CU.USER# = USERENV('SCHEMAID')
AND (RO.OWNER# = USERENV('SCHEMAID') OR RO.OBJ# IN (SELECT OA.OBJ#
FROM SYS.OBJAUTH$ OA
WHERE OA.PRIVILEGE# IN (21
, 41) AND GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)) OR EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER = -220) 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'))) ) ) SU
, SYS.DBA_RULES R WHERE SU.RULE_NAME = R.RULE_NAME(+) AND SU.OWNER = R.RULE_OWNER(+)