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.

[Home] [Help]

VIEW: SYS._USER_QUEUE_SCHEDULES

Source

View Text - Preformatted


select  q.name QNAME,
       	s.destination DESTINATION,
	s.start_time 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, obj$ jo,
	gv$scheduler_running_jobs rj, 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 	u.user# = USERENV('SCHEMAID')
and   	u.name  = t.schema
and    (rj.con_id = sys_context('USERENV', 'CON_ID') or rj.con_id IS NULL)
union all
select 	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,
        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 OR
                          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, system.aq$_queue_tables t,
        gv$propagation_sender p, sys.aq$_schedules s,
        sys.user$ u, sys.scheduler$_job j, obj$ jo,
	gv$scheduler_running_jobs rj
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 	u.user# = USERENV('SCHEMAID')
  and   u.name  = t.schema
  and   q.table_objno = t.objno
  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)

View Text - HTML Formatted

SELECT Q.NAME QNAME
, S.DESTINATION DESTINATION
, S.START_TIME 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
, OBJ$ JO
, GV$SCHEDULER_RUNNING_JOBS RJ
, 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 U.USER# = USERENV('SCHEMAID') AND U.NAME = T.SCHEMA AND (RJ.CON_ID = SYS_CONTEXT('USERENV'
, 'CON_ID') OR RJ.CON_ID IS NULL) UNION ALL SELECT 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
, 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 OR 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
, SYSTEM.AQ$_QUEUE_TABLES T
, GV$PROPAGATION_SENDER P
, SYS.AQ$_SCHEDULES S
, SYS.USER$ U
, SYS.SCHEDULER$_JOB J
, OBJ$ JO
, GV$SCHEDULER_RUNNING_JOBS RJ 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 U.USER# = USERENV('SCHEMAID')
AND U.NAME = T.SCHEMA
AND Q.TABLE_OBJNO = T.OBJNO
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)