VIEW: SYS._DBA_QUEUE_SCHEDULES_COMPAT

Source

View Text - Preformatted

select t.schema SCHEMA, q.name QNAME,
       s.destination DESTINATION,
       cast(s.start_time as timestamp(6) with time zone) START_DATE,
       substr(to_char(s.start_time,'HH24:MI:SS'),1,8) START_TIME,
       to_number(s.duration) PROPAGATION_WINDOW,
       s.next_time NEXT_TIME, to_number(s.latency) LATENCY,
       s.disabled SCHEDULE_DISABLED,
       substr(s.process_name,1,4)  PROCESS_NAME,
       decode(s.sid, NULL, NULL,
         concat(to_char(s.sid), concat(', ',to_char(s.serial)))) SESSION_ID,
       s.instance INSTANCE,
       cast( s.last_run as timestamp(6) with time zone)  LAST_RUN_DATE,
       substr(to_char(s.last_run,'HH24:MI:SS'),1,8) LAST_RUN_TIME,
       s.cur_start_time CURRENT_START_DATE,
       substr(to_char(s.cur_start_time,'HH24:MI:SS'),1,8) CURRENT_START_TIME,
       cast(s.next_run as timestamp(6) with time zone) NEXT_RUN_DATE,
       substr(to_char(s.next_run,'HH24:MI:SS'),1,8) NEXT_RUN_TIME,
       s.total_time TOTAL_TIME, s.total_msgs TOTAL_NUMBER,
       s.total_bytes TOTAL_BYTES,
       s.max_num_per_win MAX_NUMBER, s.max_size MAX_BYTES,
       s.total_msgs/decode(s.total_windows, 0, 1, s.total_windows) 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,
       s.failures 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
where s.oid  = q.oid
and   q.table_objno = t.objno
union
select p.queue_schema SCHEMA, p.queue_name QNAME,
       p.dblink DESTINATION,
       cast(s.start_time as timestamp(6) with time zone) START_DATE,
       substr(to_char(s.start_time,'HH24:MI:SS'),1,8) START_TIME,
       to_number(s.duration) PROPAGATION_WINDOW,
       s.next_time NEXT_TIME, to_number(s.latency) LATENCY,
       s.disabled SCHEDULE_DISABLED,
       substr(s.process_name,1,4)  PROCESS_NAME,
       decode(s.sid, NULL, NULL,
         concat(to_char(s.sid), concat(', ',to_char(s.serial)))) SESSION_ID,
       s.instance INSTANCE,
       cast(s.last_run as timestamp(6) with time zone) LAST_RUN_DATE,
       substr(to_char(s.last_run,'HH24:MI:SS'),1,8) LAST_RUN_TIME,
       s.cur_start_time,            -- CURRENT_START_DATE
       substr(to_char(s.cur_start_time,'HH24:MI:SS'),1,8) CURRENT_START_TIME,
       cast(s.next_run as timestamp(6) with time zone) NEXT_RUN_DATE,
       substr(to_char(s.next_run,'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/decode(s.total_windows, 0, 1, s.total_windows) AVG_NUMBER,
       p.total_bytes/decode(p.total_msgs, 0, 1, p.total_msgs) AVG_SIZE,
       s.total_time/decode(p.total_msgs, 0, 1, p.total_msgs) AVG_TIME,
       s.failures 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,
       '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, v$propagation_sender p, sys.aq$_schedules s
where q.eventid = p.queue_id
  and q.oid = s.oid
  and p.dblink = s.destination
  and (p.con_id = sys_context('USERENV', 'CON_ID') or (p.con_id IS NULL and sys_context('USERENV', 'CON_ID') IS NULL))

View Text - HTML Formatted

SELECT T.SCHEMA SCHEMA
, Q.NAME QNAME
, S.DESTINATION DESTINATION
, CAST(S.START_TIME AS TIMESTAMP(6) WITH TIME ZONE) START_DATE
, SUBSTR(TO_CHAR(S.START_TIME
, 'HH24:MI:SS')
, 1
, 8) START_TIME
, TO_NUMBER(S.DURATION) PROPAGATION_WINDOW
, S.NEXT_TIME NEXT_TIME
, TO_NUMBER(S.LATENCY) LATENCY
, S.DISABLED SCHEDULE_DISABLED
, SUBSTR(S.PROCESS_NAME
, 1
, 4) PROCESS_NAME
, DECODE(S.SID
, NULL
, NULL
, CONCAT(TO_CHAR(S.SID)
, CONCAT('
, '
, TO_CHAR(S.SERIAL)))) SESSION_ID
, S.INSTANCE INSTANCE
, CAST( S.LAST_RUN AS TIMESTAMP(6) WITH TIME ZONE) LAST_RUN_DATE
, SUBSTR(TO_CHAR(S.LAST_RUN
, 'HH24:MI:SS')
, 1
, 8) LAST_RUN_TIME
, S.CUR_START_TIME CURRENT_START_DATE
, SUBSTR(TO_CHAR(S.CUR_START_TIME
, 'HH24:MI:SS')
, 1
, 8) CURRENT_START_TIME
, CAST(S.NEXT_RUN AS TIMESTAMP(6) WITH TIME ZONE) NEXT_RUN_DATE
, SUBSTR(TO_CHAR(S.NEXT_RUN
, 'HH24:MI:SS')
, 1
, 8) NEXT_RUN_TIME
, S.TOTAL_TIME TOTAL_TIME
, S.TOTAL_MSGS TOTAL_NUMBER
, S.TOTAL_BYTES TOTAL_BYTES
, S.MAX_NUM_PER_WIN MAX_NUMBER
, S.MAX_SIZE MAX_BYTES
, S.TOTAL_MSGS/DECODE(S.TOTAL_WINDOWS
, 0
, 1
, S.TOTAL_WINDOWS) 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
, S.FAILURES 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 WHERE S.OID = Q.OID AND Q.TABLE_OBJNO = T.OBJNO UNION SELECT P.QUEUE_SCHEMA SCHEMA
, P.QUEUE_NAME QNAME
, P.DBLINK DESTINATION
, CAST(S.START_TIME AS TIMESTAMP(6) WITH TIME ZONE) START_DATE
, SUBSTR(TO_CHAR(S.START_TIME
, 'HH24:MI:SS')
, 1
, 8) START_TIME
, TO_NUMBER(S.DURATION) PROPAGATION_WINDOW
, S.NEXT_TIME NEXT_TIME
, TO_NUMBER(S.LATENCY) LATENCY
, S.DISABLED SCHEDULE_DISABLED
, SUBSTR(S.PROCESS_NAME
, 1
, 4) PROCESS_NAME
, DECODE(S.SID
, NULL
, NULL
, CONCAT(TO_CHAR(S.SID)
, CONCAT('
, '
, TO_CHAR(S.SERIAL)))) SESSION_ID
, S.INSTANCE INSTANCE
, CAST(S.LAST_RUN AS TIMESTAMP(6) WITH TIME ZONE) LAST_RUN_DATE
, SUBSTR(TO_CHAR(S.LAST_RUN
, 'HH24:MI:SS')
, 1
, 8) LAST_RUN_TIME
, S.CUR_START_TIME
, -- CURRENT_START_DATE SUBSTR(TO_CHAR(S.CUR_START_TIME
, 'HH24:MI:SS')
, 1
, 8) CURRENT_START_TIME
, CAST(S.NEXT_RUN AS TIMESTAMP(6) WITH TIME ZONE) NEXT_RUN_DATE
, SUBSTR(TO_CHAR(S.NEXT_RUN
, '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/DECODE(S.TOTAL_WINDOWS
, 0
, 1
, S.TOTAL_WINDOWS) AVG_NUMBER
, P.TOTAL_BYTES/DECODE(P.TOTAL_MSGS
, 0
, 1
, P.TOTAL_MSGS) AVG_SIZE
, S.TOTAL_TIME/DECODE(P.TOTAL_MSGS
, 0
, 1
, P.TOTAL_MSGS) AVG_TIME
, S.FAILURES 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
, '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
, V$PROPAGATION_SENDER P
, SYS.AQ$_SCHEDULES S WHERE Q.EVENTID = P.QUEUE_ID
AND Q.OID = S.OID
AND P.DBLINK = S.DESTINATION
AND (P.CON_ID = SYS_CONTEXT('USERENV'
, 'CON_ID') OR (P.CON_ID IS NULL
AND SYS_CONTEXT('USERENV'
, 'CON_ID') IS NULL))