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_SCHEDULER_RUNNING_JOBS

Source

View Text - Preformatted


SELECT jo.name, jo.subname, 'REGULAR',
       (CASE WHEN p.obj# IS NULL OR BITAND(p.flags,256) = 0
                    OR rj.job_id IS NOT NULL THEN 'FALSE'
             ELSE 'TRUE'
       END),
      rj.session_id, vp.pid,
      rj.os_process_id,
      rj.inst_id, vse.resource_consumer_group,
      CAST (systimestamp-j.last_start_date AS INTERVAL DAY(3) TO SECOND(2)),
      rj.session_stat_cpu,
      decode(bitand(j.flags, 2473901162496), 0, NULL, 2473901162496, NULL,
       substr(j.destination, 1, instr(j.destination, '"')-1)),
      decode(bitand(j.flags, 2473901162496), 0, j.destination,
        2473901162496, 'LOCAL',
        substr(j.destination, instr(j.destination, '"')+1,
           length(j.destination) - instr(j.destination, '"'))),
      j.credential_owner, j.credential_name,
      bitand(j.running_slave,18446744069414584320)/4294967295
  FROM
      scheduler$_job j JOIN obj$ jo ON
        (j.obj# = jo.obj# AND jo.owner# = USERENV('SCHEMAID'))
      LEFT OUTER JOIN gv$scheduler_running_jobs rj ON (rj.job_id = j.obj#)
      LEFT OUTER JOIN gv$session vse ON
        (rj.session_id = vse.sid AND rj.session_serial_num = vse.serial#
         AND vse.inst_id = rj.inst_id)
      LEFT OUTER JOIN gv$process vp ON
        (rj.paddr = vp.addr AND rj.inst_id = vp.inst_id)
      LEFT OUTER JOIN scheduler$_program p ON (j.program_oid = p.obj#),
      v$database v
  WHERE (BITAND(j.job_status,2) = 2 OR
          (BITAND(j.flags, 274877906944) <> 0 AND j.job_dest_id <> 0))
        AND ((j.database_role = v.database_role) OR
             (j.database_role = 'ALL')           OR
             (j.database_role is null AND v.database_role = 'PRIMARY'))
UNION ALL
  SELECT ljo.name, ljo.subname,
      decode(bitand(lj.flags, 17592186044416), 0, 'LIGHTWEIGHT',
         'IN_MEMORY_RUNTIME'),
       (CASE WHEN BITAND(lp.flags,256) = 0
            OR lrj.job_id is NOT NULL THEN 'FALSE'
             ELSE 'TRUE'
       END),
      lrj.session_id, lvp.pid,
      lrj.os_process_id,
      lrj.inst_id, lvse.resource_consumer_group,
      CAST (systimestamp-lj.last_start_date AS INTERVAL DAY(3) TO SECOND(2)),
      lrj.session_stat_cpu,
      decode(bitand(lj.flags, 2473901162496), 0, NULL, 2473901162496, NULL,
       substr(lj.destination, 1, instr(lj.destination, '"')-1)),
      decode(bitand(lj.flags, 2473901162496), 0, lj.destination,
        2473901162496, 'LOCAL',
        substr(lj.destination, instr(lj.destination, '"')+1,
           length(lj.destination) - instr(lj.destination, '"'))),
      lj.credential_owner, lj.credential_name,
      bitand(lj.running_slave,18446744069414584320)/4294967295
  FROM
      scheduler$_comb_lw_job lj JOIN scheduler$_lwjob_obj ljo ON
        (lj.obj# = ljo.obj# AND ljo.userid = USERENV('SCHEMAID'))
      LEFT OUTER JOIN scheduler$_program lp ON (lj.program_oid = lp.obj#)
      LEFT OUTER JOIN gv$scheduler_running_jobs lrj ON (lrj.job_id = lj.obj#)
      LEFT OUTER JOIN gv$session lvse ON
        (lrj.session_id = lvse.sid AND lrj.session_serial_num = lvse.serial#
         AND lvse.inst_id = lrj.inst_id)
      LEFT OUTER JOIN gv$process lvp ON
        (lrj.paddr = lvp.addr AND lrj.inst_id = lvp.inst_id)
  WHERE BITAND(lj.job_status,2) = 2
UNION ALL
  SELECT mr.name, NULL, 'IN_MEMORY_FULL', 'FALSE',
    lrj.session_id, lvp.pid,
      lrj.os_process_id, lrj.inst_id, lvse.resource_consumer_group,
    CAST (systimestamp-mr.last_start_date AS INTERVAL DAY(3) TO SECOND(2)),
      lrj.session_stat_cpu, NULL, NULL, NULL, NULL,
      bitand(mr.running_slave,18446744069414584320)/4294967295
  FROM GV$SCHEDULER_INMEM_RTINFO mr JOIN GV$SCHEDULER_INMEM_MDINFO md
    ON (mr.objid = md.objid)
    JOIN user$ mu ON (mr.userid = mu.user# AND mr.userid = USERENV('SCHEMAID'))
    LEFT OUTER JOIN scheduler$_program lp ON (md.prgoid = lp.obj#)
    LEFT OUTER JOIN gv$scheduler_running_jobs lrj ON (lrj.job_id = md.objid)
    LEFT OUTER JOIN gv$session lvse ON
        (lrj.session_id = lvse.sid AND lrj.session_serial_num = lvse.serial#
         AND lvse.inst_id = lrj.inst_id)
    LEFT OUTER JOIN gv$process lvp ON
        (lrj.paddr = lvp.addr AND lrj.inst_id = lvp.inst_id)
  WHERE BITAND(mr.job_status,2) = 2 AND mr.id_type = 2

View Text - HTML Formatted

SELECT JO.NAME
, JO.SUBNAME
, 'REGULAR'
, (CASE WHEN P.OBJ# IS NULL OR BITAND(P.FLAGS
, 256) = 0 OR RJ.JOB_ID IS NOT NULL THEN 'FALSE' ELSE 'TRUE' END)
, RJ.SESSION_ID
, VP.PID
, RJ.OS_PROCESS_ID
, RJ.INST_ID
, VSE.RESOURCE_CONSUMER_GROUP
, CAST (SYSTIMESTAMP-J.LAST_START_DATE AS INTERVAL DAY(3) TO SECOND(2))
, RJ.SESSION_STAT_CPU
, DECODE(BITAND(J.FLAGS
, 2473901162496)
, 0
, NULL
, 2473901162496
, NULL
, SUBSTR(J.DESTINATION
, 1
, INSTR(J.DESTINATION
, '"')-1))
, DECODE(BITAND(J.FLAGS
, 2473901162496)
, 0
, J.DESTINATION
, 2473901162496
, 'LOCAL'
, SUBSTR(J.DESTINATION
, INSTR(J.DESTINATION
, '"')+1
, LENGTH(J.DESTINATION) - INSTR(J.DESTINATION
, '"')))
, J.CREDENTIAL_OWNER
, J.CREDENTIAL_NAME
, BITAND(J.RUNNING_SLAVE
, 18446744069414584320)/4294967295 FROM SCHEDULER$_JOB J JOIN OBJ$ JO ON (J.OBJ# = JO.OBJ#
AND JO.OWNER# = USERENV('SCHEMAID')) LEFT OUTER JOIN GV$SCHEDULER_RUNNING_JOBS RJ ON (RJ.JOB_ID = J.OBJ#) LEFT OUTER JOIN GV$SESSION VSE ON (RJ.SESSION_ID = VSE.SID
AND RJ.SESSION_SERIAL_NUM = VSE.SERIAL#
AND VSE.INST_ID = RJ.INST_ID) LEFT OUTER JOIN GV$PROCESS VP ON (RJ.PADDR = VP.ADDR
AND RJ.INST_ID = VP.INST_ID) LEFT OUTER JOIN SCHEDULER$_PROGRAM P ON (J.PROGRAM_OID = P.OBJ#)
, V$DATABASE V
WHERE (BITAND(J.JOB_STATUS
, 2) = 2 OR (BITAND(J.FLAGS
, 274877906944) <> 0
AND J.JOB_DEST_ID <> 0))
AND ((J.DATABASE_ROLE = V.DATABASE_ROLE) OR (J.DATABASE_ROLE = 'ALL') OR (J.DATABASE_ROLE IS NULL
AND V.DATABASE_ROLE = 'PRIMARY')) UNION ALL SELECT LJO.NAME
, LJO.SUBNAME
, DECODE(BITAND(LJ.FLAGS
, 17592186044416)
, 0
, 'LIGHTWEIGHT'
, 'IN_MEMORY_RUNTIME')
, (CASE WHEN BITAND(LP.FLAGS
, 256) = 0 OR LRJ.JOB_ID IS NOT NULL THEN 'FALSE' ELSE 'TRUE' END)
, LRJ.SESSION_ID
, LVP.PID
, LRJ.OS_PROCESS_ID
, LRJ.INST_ID
, LVSE.RESOURCE_CONSUMER_GROUP
, CAST (SYSTIMESTAMP-LJ.LAST_START_DATE AS INTERVAL DAY(3) TO SECOND(2))
, LRJ.SESSION_STAT_CPU
, DECODE(BITAND(LJ.FLAGS
, 2473901162496)
, 0
, NULL
, 2473901162496
, NULL
, SUBSTR(LJ.DESTINATION
, 1
, INSTR(LJ.DESTINATION
, '"')-1))
, DECODE(BITAND(LJ.FLAGS
, 2473901162496)
, 0
, LJ.DESTINATION
, 2473901162496
, 'LOCAL'
, SUBSTR(LJ.DESTINATION
, INSTR(LJ.DESTINATION
, '"')+1
, LENGTH(LJ.DESTINATION) - INSTR(LJ.DESTINATION
, '"')))
, LJ.CREDENTIAL_OWNER
, LJ.CREDENTIAL_NAME
, BITAND(LJ.RUNNING_SLAVE
, 18446744069414584320)/4294967295 FROM SCHEDULER$_COMB_LW_JOB LJ JOIN SCHEDULER$_LWJOB_OBJ LJO ON (LJ.OBJ# = LJO.OBJ#
AND LJO.USERID = USERENV('SCHEMAID')) LEFT OUTER JOIN SCHEDULER$_PROGRAM LP ON (LJ.PROGRAM_OID = LP.OBJ#) LEFT OUTER JOIN GV$SCHEDULER_RUNNING_JOBS LRJ ON (LRJ.JOB_ID = LJ.OBJ#) LEFT OUTER JOIN GV$SESSION LVSE ON (LRJ.SESSION_ID = LVSE.SID
AND LRJ.SESSION_SERIAL_NUM = LVSE.SERIAL#
AND LVSE.INST_ID = LRJ.INST_ID) LEFT OUTER JOIN GV$PROCESS LVP ON (LRJ.PADDR = LVP.ADDR
AND LRJ.INST_ID = LVP.INST_ID)
WHERE BITAND(LJ.JOB_STATUS
, 2) = 2 UNION ALL SELECT MR.NAME
, NULL
, 'IN_MEMORY_FULL'
, 'FALSE'
, LRJ.SESSION_ID
, LVP.PID
, LRJ.OS_PROCESS_ID
, LRJ.INST_ID
, LVSE.RESOURCE_CONSUMER_GROUP
, CAST (SYSTIMESTAMP-MR.LAST_START_DATE AS INTERVAL DAY(3) TO SECOND(2))
, LRJ.SESSION_STAT_CPU
, NULL
, NULL
, NULL
, NULL
, BITAND(MR.RUNNING_SLAVE
, 18446744069414584320)/4294967295
FROM GV$SCHEDULER_INMEM_RTINFO MR JOIN GV$SCHEDULER_INMEM_MDINFO MD ON (MR.OBJID = MD.OBJID) JOIN USER$ MU ON (MR.USERID = MU.USER#
AND MR.USERID = USERENV('SCHEMAID')) LEFT OUTER JOIN SCHEDULER$_PROGRAM LP ON (MD.PRGOID = LP.OBJ#) LEFT OUTER JOIN GV$SCHEDULER_RUNNING_JOBS LRJ ON (LRJ.JOB_ID = MD.OBJID) LEFT OUTER JOIN GV$SESSION LVSE ON (LRJ.SESSION_ID = LVSE.SID
AND LRJ.SESSION_SERIAL_NUM = LVSE.SERIAL#
AND LVSE.INST_ID = LRJ.INST_ID) LEFT OUTER JOIN GV$PROCESS LVP ON (LRJ.PADDR = LVP.ADDR
AND LRJ.INST_ID = LVP.INST_ID)
WHERE BITAND(MR.JOB_STATUS
, 2) = 2
AND MR.ID_TYPE = 2