eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: SYS.ALL_QUEUE_TABLES

Source

View Text - Preformatted


select t.schema OWNER, t.name QUEUE_TABLE,
     decode(t.udata_type, 1 , 'OBJECT', 2, 'VARIANT', 3, 'RAW', 5, 'JMS_BYTES') TYPE,
     u.name || '.' || o.name OBJECT_TYPE,
     decode(t.sort_cols, 0, 'NONE', 1, 'PRIORITY', 2, 'ENQUEUE_TIME',
                               3, 'PRIORITY, ENQUEUE_TIME',
                               4, 'COMMIT_TIME',
                               5, 'PRIORITY, COMMIT_TIME',
                               7, 'ENQUEUE_TIME, PRIORITY') SORT_ORDER,
     decode(bitand(t.flags, 1), 1, 'MULTIPLE', 0, 'SINGLE') RECIPIENTS,
     decode(bitand(t.flags, 2), 2, 'TRANSACTIONAL', 0, 'NONE')MESSAGE_GROUPING,
     decode(bitand(t.flags, 8192+8), 8192+8, '10.0.0', 8, '8.1.3', 0, '8.0.3')COMPATIBLE,
     aft.primary_instance PRIMARY_INSTANCE,
     aft.secondary_instance SECONDARY_INSTANCE,
     aft.owner_instance OWNER_INSTANCE,
     substr(t.table_comment, 1, 50) USER_COMMENT,
     decode(bitand(t.flags, 4096), 4096, 'YES', 0, 'NO') SECURE
from system.aq$_queue_tables t, sys.col$ c, sys.coltype$ ct, sys.obj$ o,
sys.user$ u, sys.aq$_queue_table_affinities aft
where c.intcol# = ct.intcol#
and c.obj# = ct.obj#
and c.name = 'USER_DATA'
and t.objno = c.obj#
and o.oid$ = ct.toid
and o.type# = 13
and o.owner# = u.user#
and t.objno = aft.table_objno
and t.objno in
(select q.table_objno
 from system.aq$_queues q, sys.obj$ ro
 where 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')))
     )
)
union
select t.schema OWNER, t.name QUEUE_TABLE,
     decode(t.udata_type, 1 , 'OBJECT', 2, 'VARIANT', 3, 'RAW', 5, 'JMS_BYTES') TYPE,
     null OBJECT_TYPE,
     decode(t.sort_cols, 0, 'NONE', 1, 'PRIORITY', 2, 'ENQUEUE_TIME',
                               3, 'PRIORITY, ENQUEUE_TIME',
                               4, 'COMMIT_TIME',
                               5, 'PRIORITY, COMMIT_TIME',
                               7, 'ENQUEUE_TIME, PRIORITY') SORT_ORDER,
     decode(bitand(t.flags, 1), 1, 'MULTIPLE', 0, 'SINGLE') RECIPIENTS,
     decode(bitand(t.flags, 2), 2, 'TRANSACTIONAL', 0, 'NONE')MESSAGE_GROUPING,
     decode(bitand(t.flags, 8192+8), 8192+8, '10.0.0', 8, '8.1.3', 0, '8.0.3')COMPATIBLE,
     aft.primary_instance PRIMARY_INSTANCE,
     aft.secondary_instance SECONDARY_INSTANCE,
     aft.owner_instance OWNER_INSTANCE,
     substr(t.table_comment, 1, 50) USER_COMMENT,
     decode(bitand(t.flags, 4096), 4096, 'YES', 0, 'NO') SECURE
from system.aq$_queue_tables t, sys.aq$_queue_table_affinities aft
where (t.udata_type = 2
or t.udata_type = 3 or t.udata_type = 5)
and t.objno = aft.table_objno
and t.objno in
(select q.table_objno
 from system.aq$_queues q, sys.obj$ ro
 where 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')))
     )
)

View Text - HTML Formatted

SELECT T.SCHEMA OWNER
, T.NAME QUEUE_TABLE
, DECODE(T.UDATA_TYPE
, 1
, 'OBJECT'
, 2
, 'VARIANT'
, 3
, 'RAW'
, 5
, 'JMS_BYTES') TYPE
, U.NAME || '.' || O.NAME OBJECT_TYPE
, DECODE(T.SORT_COLS
, 0
, 'NONE'
, 1
, 'PRIORITY'
, 2
, 'ENQUEUE_TIME'
, 3
, 'PRIORITY
, ENQUEUE_TIME'
, 4
, 'COMMIT_TIME'
, 5
, 'PRIORITY
, COMMIT_TIME'
, 7
, 'ENQUEUE_TIME
, PRIORITY') SORT_ORDER
, DECODE(BITAND(T.FLAGS
, 1)
, 1
, 'MULTIPLE'
, 0
, 'SINGLE') RECIPIENTS
, DECODE(BITAND(T.FLAGS
, 2)
, 2
, 'TRANSACTIONAL'
, 0
, 'NONE')MESSAGE_GROUPING
, DECODE(BITAND(T.FLAGS
, 8192+8)
, 8192+8
, '10.0.0'
, 8
, '8.1.3'
, 0
, '8.0.3')COMPATIBLE
, AFT.PRIMARY_INSTANCE PRIMARY_INSTANCE
, AFT.SECONDARY_INSTANCE SECONDARY_INSTANCE
, AFT.OWNER_INSTANCE OWNER_INSTANCE
, SUBSTR(T.TABLE_COMMENT
, 1
, 50) USER_COMMENT
, DECODE(BITAND(T.FLAGS
, 4096)
, 4096
, 'YES'
, 0
, 'NO') SECURE FROM SYSTEM.AQ$_QUEUE_TABLES T
, SYS.COL$ C
, SYS.COLTYPE$ CT
, SYS.OBJ$ O
, SYS.USER$ U
, SYS.AQ$_QUEUE_TABLE_AFFINITIES AFT WHERE C.INTCOL# = CT.INTCOL# AND C.OBJ# = CT.OBJ# AND C.NAME = 'USER_DATA' AND T.OBJNO = C.OBJ# AND O.OID$ = CT.TOID AND O.TYPE# = 13 AND O.OWNER# = U.USER# AND T.OBJNO = AFT.TABLE_OBJNO AND T.OBJNO IN (SELECT Q.TABLE_OBJNO
FROM SYSTEM.AQ$_QUEUES Q
, SYS.OBJ$ RO
WHERE 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'))) ) ) UNION SELECT T.SCHEMA OWNER
, T.NAME QUEUE_TABLE
, DECODE(T.UDATA_TYPE
, 1
, 'OBJECT'
, 2
, 'VARIANT'
, 3
, 'RAW'
, 5
, 'JMS_BYTES') TYPE
, NULL OBJECT_TYPE
, DECODE(T.SORT_COLS
, 0
, 'NONE'
, 1
, 'PRIORITY'
, 2
, 'ENQUEUE_TIME'
, 3
, 'PRIORITY
, ENQUEUE_TIME'
, 4
, 'COMMIT_TIME'
, 5
, 'PRIORITY
, COMMIT_TIME'
, 7
, 'ENQUEUE_TIME
, PRIORITY') SORT_ORDER
, DECODE(BITAND(T.FLAGS
, 1)
, 1
, 'MULTIPLE'
, 0
, 'SINGLE') RECIPIENTS
, DECODE(BITAND(T.FLAGS
, 2)
, 2
, 'TRANSACTIONAL'
, 0
, 'NONE')MESSAGE_GROUPING
, DECODE(BITAND(T.FLAGS
, 8192+8)
, 8192+8
, '10.0.0'
, 8
, '8.1.3'
, 0
, '8.0.3')COMPATIBLE
, AFT.PRIMARY_INSTANCE PRIMARY_INSTANCE
, AFT.SECONDARY_INSTANCE SECONDARY_INSTANCE
, AFT.OWNER_INSTANCE OWNER_INSTANCE
, SUBSTR(T.TABLE_COMMENT
, 1
, 50) USER_COMMENT
, DECODE(BITAND(T.FLAGS
, 4096)
, 4096
, 'YES'
, 0
, 'NO') SECURE FROM SYSTEM.AQ$_QUEUE_TABLES T
, SYS.AQ$_QUEUE_TABLE_AFFINITIES AFT WHERE (T.UDATA_TYPE = 2 OR T.UDATA_TYPE = 3 OR T.UDATA_TYPE = 5) AND T.OBJNO = AFT.TABLE_OBJNO AND T.OBJNO IN (SELECT Q.TABLE_OBJNO
FROM SYSTEM.AQ$_QUEUES Q
, SYS.OBJ$ RO
WHERE 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'))) ) )