VIEW: SYS._ALL_QUEUE_SCHEDULES

Source

View Text - Preformatted

select t.schema SCHEMA, q.name QNAME,
        s.destination DESTINATION,
        j.start_date START_DATE,
        substr(to_char(j.start_date,'HH24:MI:SS'),1,8) START_TIME,
        to_number(s.duration) PROPAGATION_WINDOW,
        DECODE(BITAND(j.flags,1024+4096+134217728), 0, j.schedule_expr, NULL)
        NEXT_TIME, to_number(s.latency) LATENCY,
        decode(BITAND(j.job_status,1), 0, 'Y', 'N') SCHEDULE_DISABLED,
        (select substr(v.program, LENGTH(v.program)-4, 4)
          from gv$process v where v.inst_id = j.instance_id and
          (v.con_id = sys_context('USERENV', 'CON_ID') or
           v.con_id is null and sys_context('USERENV', 'CON_ID') is null) and
          v.spid = rj.os_process_id) PROCESS_NAME,
        (select concat(to_char(rj.session_id), concat(', ', to_char(vs.serial#)))
          from gv$session vs where vs.sid = rj.session_id and
          (vs.con_id = sys_context('USERENV', 'CON_ID') OR
           vs.con_id is null and sys_context('USERENV', 'CON_ID') is null) and
          vs.inst_id = j.instance_id) SESSION_ID,
        j.instance_id INSTANCE,
        j.last_start_date LAST_RUN_DATE,
        substr(to_char(j.last_start_date,'HH24:MI:SS'),1,8) LAST_RUN_TIME,
        decode(BITAND(j.job_status,2+65536), 2, j.last_start_date, NULL) CURRENT_START_DATE,
        decode(BITAND(j.job_status,2+65536), 2, substr(to_char(j.last_start_date,'HH24:MI:SS'),1,8),NULL) CURRENT_START_TIME,
        j.next_run_date NEXT_RUN_DATE,
        substr(to_char(j.next_run_date,'HH24:MI:SS'),1,8) NEXT_RUN_TIME,
        s.total_time TOTAL_TIME,
        s.total_msgs TOTAL_NUMBER,
        s.total_bytes TOTAL_BYTES,
        s.total_msgs MAX_NUMBER, s.max_size MAX_BYTES,
        s.total_msgs/GREATEST(1, (select count (*) from dba_scheduler_job_run_details where job_name = s.job_name)) AVG_NUMBER,
        s.total_bytes/decode(s.total_msgs, 0, 1, s.total_msgs) AVG_SIZE,
        s.total_time/decode(s.total_msgs, 0, 1, s.total_msgs) AVG_TIME,
        decode(j.failure_count, 1, 16, j.retry_count) FAILURES,
        s.error_time LAST_ERROR_DATE,
        substr(to_char(s.error_time,'HH24:MI:SS'),1,8) LAST_ERROR_TIME,
        s.last_error_msg LAST_ERROR_MSG,
        'PERSISTENT' MESSAGE_DELIVERY_MODE,
        null ELAPSED_DEQUEUE_TIME, null ELAPSED_PICKLE_TIME,
        s.job_name JOB_NAME
from    system.aq$_queues q, system.aq$_queue_tables t,
        sys.aq$_schedules s, sys.scheduler$_job j,
        gv$scheduler_running_jobs rj, sys.obj$ ro, sys.obj$ jo,
        dba_services d, sys.user$ u
where   s.oid  = q.oid
and     s.job_name = jo.name
and     j.obj# = jo.obj#
and     rj.job_id (+)= j.obj#
and     q.table_objno = t.objno
and     ro.owner# = u.user#
and     ro.obj# = q.eventid
and     (rj.con_id = sys_context('USERENV', 'CON_ID') or rj.con_id IS NULL)
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 = d.name (+)
union all
select  p.queue_schema SCHEMA, p.queue_name QNAME,
        p.dblink DESTINATION, j.start_date START_DATE,
        substr(to_char(j.start_date,'HH24:MI:SS'),1,8) START_TIME,
        to_number(s.duration) PROPAGATION_WINDOW,
        DECODE(BITAND(j.flags,1024+4096+134217728), 0, j.schedule_expr, NULL)
        NEXT_TIME,
        decode(p.schedule_status, 'SCHEDULE OPTIMIZED',
               p.last_lcr_latency, to_number(s.latency)) LATENCY,
        decode(BITAND(j.job_status,1), 0, 'Y', 'N') SCHEDULE_DISABLED,
        (select substr(v.program, LENGTH(v.program)-4, 4)
          from gv$process v where v.inst_id = j.instance_id and
          (v.con_id = sys_context('USERENV', 'CON_ID') OR
           v.con_id is null and sys_context('USERENV', 'CON_ID') is null )and
          v.spid = decode(p.schedule_status,
                          'SCHEDULE OPTIMIZED', p.spid,
                          rj.os_process_id)) PROCESS_NAME,
        case when p.schedule_status = 'SCHEDULE OPTIMIZED'
             then (p.session_id || ', ' || p.serial#)
             else (select concat(to_char(rj.session_id),
                          concat(', ', to_char(vs.serial#)))
                   from gv$session vs
                   where vs.sid = rj.session_id and vs.inst_id = j.instance_id
                   and (vs.con_id = sys_context('USERENV', 'CON_ID') OR
                        vs.con_id is null AND
                    sys_context('USERENV', 'CON_ID') is null))
        end SESSION_ID,
        j.instance_id INSTANCE,
        decode(p.schedule_status, 'SCHEDULE OPTIMIZED',
               cast(p.last_lcr_propagation_time as timestamp with time zone), j.last_start_date)
        LAST_RUN_DATE,
        decode(p.schedule_status, 'SCHEDULE OPTIMIZED',
               substr(to_char(p.last_lcr_propagation_time, 'HH24:MI:SS'),1,8),
               substr(to_char(j.last_start_date,'HH24:MI:SS'),1,8))
        LAST_RUN_TIME,
        decode(BITAND(j.job_status,2+65536),2, j.last_start_date,
               decode(p.schedule_status, 'SCHEDULE OPTIMIZED',
                      cast(p.startup_time as timestamp with time zone), NULL))
        CURRENT_START_DATE,
        decode(BITAND(j.job_status,2+65536),2,
               substr(to_char(j.last_start_date,'HH24:MI:SS'),1,8),
               decode(p.schedule_status, 'SCHEDULE OPTIMIZED',
                      substr(to_char(p.startup_time,'HH24:MI:SS'),1,8), NULL))
        CURRENT_START_TIME,
        j.next_run_date NEXT_RUN_DATE,
        substr(to_char(j.next_run_date,'HH24:MI:SS'),1,8) NEXT_RUN_TIME,
        p.elapsed_propagation_time/100 TOTAL_TIME, p.total_msgs TOTAL_NUMBER,
        p.total_bytes TOTAL_BYTES,
        p.max_num_per_win MAX_NUMBER, p.max_size MAX_BYTES,
        p.total_msgs/GREATEST(1, (select count (*) from dba_scheduler_job_run_details where job_name = s.job_name)) AVG_NUMBER,
        p.total_bytes/decode(p.total_msgs, 0, 1, p.total_msgs) AVG_SIZE,
        (p.elapsed_propagation_time/100)/decode(p.total_msgs, 0, 1, p.total_msgs) AVG_TIME,
        decode(p.schedule_status, 'SCHEDULE OPTIMIZED', 0,
                decode(j.failure_count, 1, 16, j.retry_count)) FAILURES,
        decode(p.schedule_status, 'SCHEDULE OPTIMIZED', to_date(NULL),
               s.error_time) LAST_ERROR_DATE,
        decode(p.schedule_status, 'SCHEDULE OPTIMIZED', NULL,
               substr(to_char(s.error_time,'HH24:MI:SS'),1,8)) LAST_ERROR_TIME,
        decode(p.schedule_status, 'SCHEDULE OPTIMIZED', NULL,
               s.last_error_msg) LAST_ERROR_MSG,
        'BUFFERED' MESSAGE_DELIVERY_MODE,
        p.elapsed_dequeue_time/100 ELAPSED_DEQUEUE_TIME,
        p.elapsed_pickle_time/100 ELAPSED_PICKLE_TIME,
        s.job_name JOB_NAME
from    system.aq$_queues q, gv$propagation_sender p,
        sys.aq$_schedules s, sys.scheduler$_job j, obj$ jo,
        gv$scheduler_running_jobs rj,sys.obj$ ro,
        dba_services d, sys.user$ u
where   q.eventid = p.queue_id
  and   q.oid = s.oid
  and   s.job_name = jo.name
  and   jo.obj# = j.obj#
  and   rj.job_id (+)= j.obj#
  and   p.dblink = s.destination
  and   ro.owner# = u.user#
  and   ro.obj# = q.eventid
  and  (p.con_id = sys_context('USERENV', 'CON_ID') or p.con_id IS NULL AND sys_context('USERENV', 'CON_ID') IS NULL)
  and  (rj.con_id = sys_context('USERENV', 'CON_ID') or rj.con_id IS NULL)
  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 = d.name (+)

View Text - HTML Formatted

SELECT T.SCHEMA SCHEMA
, Q.NAME QNAME
, S.DESTINATION DESTINATION
, J.START_DATE START_DATE
, SUBSTR(TO_CHAR(J.START_DATE
, 'HH24:MI:SS')
, 1
, 8) START_TIME
, TO_NUMBER(S.DURATION) PROPAGATION_WINDOW
, DECODE(BITAND(J.FLAGS
, 1024+4096+134217728)
, 0
, J.SCHEDULE_EXPR
, NULL) NEXT_TIME
, TO_NUMBER(S.LATENCY) LATENCY
, DECODE(BITAND(J.JOB_STATUS
, 1)
, 0
, 'Y'
, 'N') SCHEDULE_DISABLED
, (SELECT SUBSTR(V.PROGRAM
, LENGTH(V.PROGRAM)-4
, 4)
FROM GV$PROCESS V
WHERE V.INST_ID = J.INSTANCE_ID AND (V.CON_ID = SYS_CONTEXT('USERENV'
, 'CON_ID') OR V.CON_ID IS NULL
AND SYS_CONTEXT('USERENV'
, 'CON_ID') IS NULL) AND V.SPID = RJ.OS_PROCESS_ID) PROCESS_NAME
, (SELECT CONCAT(TO_CHAR(RJ.SESSION_ID)
, CONCAT('
, '
, TO_CHAR(VS.SERIAL#)))
FROM GV$SESSION VS
WHERE VS.SID = RJ.SESSION_ID AND (VS.CON_ID = SYS_CONTEXT('USERENV'
, 'CON_ID') OR VS.CON_ID IS NULL
AND SYS_CONTEXT('USERENV'
, 'CON_ID') IS NULL) AND VS.INST_ID = J.INSTANCE_ID) SESSION_ID
, J.INSTANCE_ID INSTANCE
, J.LAST_START_DATE LAST_RUN_DATE
, SUBSTR(TO_CHAR(J.LAST_START_DATE
, 'HH24:MI:SS')
, 1
, 8) LAST_RUN_TIME
, DECODE(BITAND(J.JOB_STATUS
, 2+65536)
, 2
, J.LAST_START_DATE
, NULL) CURRENT_START_DATE
, DECODE(BITAND(J.JOB_STATUS
, 2+65536)
, 2
, SUBSTR(TO_CHAR(J.LAST_START_DATE
, 'HH24:MI:SS')
, 1
, 8)
, NULL) CURRENT_START_TIME
, J.NEXT_RUN_DATE NEXT_RUN_DATE
, SUBSTR(TO_CHAR(J.NEXT_RUN_DATE
, 'HH24:MI:SS')
, 1
, 8) NEXT_RUN_TIME
, S.TOTAL_TIME TOTAL_TIME
, S.TOTAL_MSGS TOTAL_NUMBER
, S.TOTAL_BYTES TOTAL_BYTES
, S.TOTAL_MSGS MAX_NUMBER
, S.MAX_SIZE MAX_BYTES
, S.TOTAL_MSGS/GREATEST(1
, (SELECT COUNT (*)
FROM DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE JOB_NAME = S.JOB_NAME)) AVG_NUMBER
, S.TOTAL_BYTES/DECODE(S.TOTAL_MSGS
, 0
, 1
, S.TOTAL_MSGS) AVG_SIZE
, S.TOTAL_TIME/DECODE(S.TOTAL_MSGS
, 0
, 1
, S.TOTAL_MSGS) AVG_TIME
, DECODE(J.FAILURE_COUNT
, 1
, 16
, J.RETRY_COUNT) FAILURES
, S.ERROR_TIME LAST_ERROR_DATE
, SUBSTR(TO_CHAR(S.ERROR_TIME
, 'HH24:MI:SS')
, 1
, 8) LAST_ERROR_TIME
, S.LAST_ERROR_MSG LAST_ERROR_MSG
, 'PERSISTENT' MESSAGE_DELIVERY_MODE
, NULL ELAPSED_DEQUEUE_TIME
, NULL ELAPSED_PICKLE_TIME
, S.JOB_NAME JOB_NAME FROM SYSTEM.AQ$_QUEUES Q
, SYSTEM.AQ$_QUEUE_TABLES T
, SYS.AQ$_SCHEDULES S
, SYS.SCHEDULER$_JOB J
, GV$SCHEDULER_RUNNING_JOBS RJ
, SYS.OBJ$ RO
, SYS.OBJ$ JO
, DBA_SERVICES D
, SYS.USER$ U WHERE S.OID = Q.OID AND S.JOB_NAME = JO.NAME AND J.OBJ# = JO.OBJ# AND RJ.JOB_ID (+)= J.OBJ# AND Q.TABLE_OBJNO = T.OBJNO AND RO.OWNER# = U.USER# AND RO.OBJ# = Q.EVENTID AND (RJ.CON_ID = SYS_CONTEXT('USERENV'
, 'CON_ID') OR RJ.CON_ID IS NULL) 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 = D.NAME (+) UNION ALL SELECT P.QUEUE_SCHEMA SCHEMA
, P.QUEUE_NAME QNAME
, P.DBLINK DESTINATION
, J.START_DATE START_DATE
, SUBSTR(TO_CHAR(J.START_DATE
, 'HH24:MI:SS')
, 1
, 8) START_TIME
, TO_NUMBER(S.DURATION) PROPAGATION_WINDOW
, DECODE(BITAND(J.FLAGS
, 1024+4096+134217728)
, 0
, J.SCHEDULE_EXPR
, NULL) NEXT_TIME
, DECODE(P.SCHEDULE_STATUS
, 'SCHEDULE OPTIMIZED'
, P.LAST_LCR_LATENCY
, TO_NUMBER(S.LATENCY)) LATENCY
, DECODE(BITAND(J.JOB_STATUS
, 1)
, 0
, 'Y'
, 'N') SCHEDULE_DISABLED
, (SELECT SUBSTR(V.PROGRAM
, LENGTH(V.PROGRAM)-4
, 4)
FROM GV$PROCESS V
WHERE V.INST_ID = J.INSTANCE_ID AND (V.CON_ID = SYS_CONTEXT('USERENV'
, 'CON_ID') OR V.CON_ID IS NULL
AND SYS_CONTEXT('USERENV'
, 'CON_ID') IS NULL )AND V.SPID = DECODE(P.SCHEDULE_STATUS
, 'SCHEDULE OPTIMIZED'
, P.SPID
, RJ.OS_PROCESS_ID)) PROCESS_NAME
, CASE WHEN P.SCHEDULE_STATUS = 'SCHEDULE OPTIMIZED' THEN (P.SESSION_ID || '
, ' || P.SERIAL#) ELSE (SELECT CONCAT(TO_CHAR(RJ.SESSION_ID)
, CONCAT('
, '
, TO_CHAR(VS.SERIAL#)))
FROM GV$SESSION VS
WHERE VS.SID = RJ.SESSION_ID
AND VS.INST_ID = J.INSTANCE_ID
AND (VS.CON_ID = SYS_CONTEXT('USERENV'
, 'CON_ID') OR VS.CON_ID IS NULL AND SYS_CONTEXT('USERENV'
, 'CON_ID') IS NULL)) END SESSION_ID
, J.INSTANCE_ID INSTANCE
, DECODE(P.SCHEDULE_STATUS
, 'SCHEDULE OPTIMIZED'
, CAST(P.LAST_LCR_PROPAGATION_TIME AS TIMESTAMP WITH TIME ZONE)
, J.LAST_START_DATE) LAST_RUN_DATE
, DECODE(P.SCHEDULE_STATUS
, 'SCHEDULE OPTIMIZED'
, SUBSTR(TO_CHAR(P.LAST_LCR_PROPAGATION_TIME
, 'HH24:MI:SS')
, 1
, 8)
, SUBSTR(TO_CHAR(J.LAST_START_DATE
, 'HH24:MI:SS')
, 1
, 8)) LAST_RUN_TIME
, DECODE(BITAND(J.JOB_STATUS
, 2+65536)
, 2
, J.LAST_START_DATE
, DECODE(P.SCHEDULE_STATUS
, 'SCHEDULE OPTIMIZED'
, CAST(P.STARTUP_TIME AS TIMESTAMP WITH TIME ZONE)
, NULL)) CURRENT_START_DATE
, DECODE(BITAND(J.JOB_STATUS
, 2+65536)
, 2
, SUBSTR(TO_CHAR(J.LAST_START_DATE
, 'HH24:MI:SS')
, 1
, 8)
, DECODE(P.SCHEDULE_STATUS
, 'SCHEDULE OPTIMIZED'
, SUBSTR(TO_CHAR(P.STARTUP_TIME
, 'HH24:MI:SS')
, 1
, 8)
, NULL)) CURRENT_START_TIME
, J.NEXT_RUN_DATE NEXT_RUN_DATE
, SUBSTR(TO_CHAR(J.NEXT_RUN_DATE
, 'HH24:MI:SS')
, 1
, 8) NEXT_RUN_TIME
, P.ELAPSED_PROPAGATION_TIME/100 TOTAL_TIME
, P.TOTAL_MSGS TOTAL_NUMBER
, P.TOTAL_BYTES TOTAL_BYTES
, P.MAX_NUM_PER_WIN MAX_NUMBER
, P.MAX_SIZE MAX_BYTES
, P.TOTAL_MSGS/GREATEST(1
, (SELECT COUNT (*)
FROM DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE JOB_NAME = S.JOB_NAME)) AVG_NUMBER
, P.TOTAL_BYTES/DECODE(P.TOTAL_MSGS
, 0
, 1
, P.TOTAL_MSGS) AVG_SIZE
, (P.ELAPSED_PROPAGATION_TIME/100)/DECODE(P.TOTAL_MSGS
, 0
, 1
, P.TOTAL_MSGS) AVG_TIME
, DECODE(P.SCHEDULE_STATUS
, 'SCHEDULE OPTIMIZED'
, 0
, DECODE(J.FAILURE_COUNT
, 1
, 16
, J.RETRY_COUNT)) FAILURES
, DECODE(P.SCHEDULE_STATUS
, 'SCHEDULE OPTIMIZED'
, TO_DATE(NULL)
, S.ERROR_TIME) LAST_ERROR_DATE
, DECODE(P.SCHEDULE_STATUS
, 'SCHEDULE OPTIMIZED'
, NULL
, SUBSTR(TO_CHAR(S.ERROR_TIME
, 'HH24:MI:SS')
, 1
, 8)) LAST_ERROR_TIME
, DECODE(P.SCHEDULE_STATUS
, 'SCHEDULE OPTIMIZED'
, NULL
, S.LAST_ERROR_MSG) LAST_ERROR_MSG
, 'BUFFERED' MESSAGE_DELIVERY_MODE
, P.ELAPSED_DEQUEUE_TIME/100 ELAPSED_DEQUEUE_TIME
, P.ELAPSED_PICKLE_TIME/100 ELAPSED_PICKLE_TIME
, S.JOB_NAME JOB_NAME FROM SYSTEM.AQ$_QUEUES Q
, GV$PROPAGATION_SENDER P
, SYS.AQ$_SCHEDULES S
, SYS.SCHEDULER$_JOB J
, OBJ$ JO
, GV$SCHEDULER_RUNNING_JOBS RJ
, SYS.OBJ$ RO
, DBA_SERVICES D
, SYS.USER$ U WHERE Q.EVENTID = P.QUEUE_ID
AND Q.OID = S.OID
AND S.JOB_NAME = JO.NAME
AND JO.OBJ# = J.OBJ#
AND RJ.JOB_ID (+)= J.OBJ#
AND P.DBLINK = S.DESTINATION
AND RO.OWNER# = U.USER#
AND RO.OBJ# = Q.EVENTID
AND (P.CON_ID = SYS_CONTEXT('USERENV'
, 'CON_ID') OR P.CON_ID IS NULL
AND SYS_CONTEXT('USERENV'
, 'CON_ID') IS NULL)
AND (RJ.CON_ID = SYS_CONTEXT('USERENV'
, 'CON_ID') OR RJ.CON_ID IS NULL)
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 = D.NAME (+)