VIEW: SYS.DBA_QUEUE_TABLES

Source

View Text - Preformatted

select t.schema OWNER, t.name QUEUE_TABLE,
     decode(t.udata_type, 1 , 'OBJECT', 2, 'VARIANT', 3, 'RAW') 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
union
select t.schema OWNER, t.name QUEUE_TABLE,
     decode(t.udata_type, 1 , 'OBJECT', 2, 'VARIANT', 3, 'RAW') 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)
and t.objno = aft.table_objno

View Text - HTML Formatted

SELECT T.SCHEMA OWNER
, T.NAME QUEUE_TABLE
, DECODE(T.UDATA_TYPE
, 1
, 'OBJECT'
, 2
, 'VARIANT'
, 3
, 'RAW') 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 UNION SELECT T.SCHEMA OWNER
, T.NAME QUEUE_TABLE
, DECODE(T.UDATA_TYPE
, 1
, 'OBJECT'
, 2
, 'VARIANT'
, 3
, 'RAW') 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) AND T.OBJNO = AFT.TABLE_OBJNO