VIEW: SYS.DBA_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 t.schema OWNER, q.name QUEUE_NAME, t.name QUEUE_TABLE,
                s.name CONSUMER_NAME, s.address ADDRESS, s.protocol PROTOCOL,
                s.trans_name TRANSFORMATION, s.rule_name RULE_NAME,
                s.sub_type SUB_TYPE, s.subscriber_id SUBSCRIBER_ID,
                s.pos_bitmap POS_BITMAP
         FROM   system.aq$_queues q, system.aq$_queue_tables t,
         TABLE(aq$_get_subscribers(t.schema, q.name, t.name,
                                   NULL, q.eventid, t.flags)) s
         where q.table_objno = t.objno
         and   bitand(t.flags, 1) = 1 and q.usage!=1
         ) su, 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 T.SCHEMA OWNER
, Q.NAME QUEUE_NAME
, T.NAME QUEUE_TABLE
, S.NAME CONSUMER_NAME
, S.ADDRESS ADDRESS
, S.PROTOCOL PROTOCOL
, S.TRANS_NAME TRANSFORMATION
, S.RULE_NAME RULE_NAME
, S.SUB_TYPE SUB_TYPE
, S.SUBSCRIBER_ID SUBSCRIBER_ID
, S.POS_BITMAP POS_BITMAP
FROM SYSTEM.AQ$_QUEUES Q
, SYSTEM.AQ$_QUEUE_TABLES T
, TABLE(AQ$_GET_SUBSCRIBERS(T.SCHEMA
, Q.NAME
, T.NAME
, NULL
, Q.EVENTID
, T.FLAGS)) S
WHERE Q.TABLE_OBJNO = T.OBJNO
AND BITAND(T.FLAGS
, 1) = 1
AND Q.USAGE!=1 ) SU
, DBA_RULES R WHERE SU.RULE_NAME = R.RULE_NAME(+) AND SU.OWNER = R.RULE_OWNER(+)