VIEW: SYS.ALL_SCHEDULER_RUNNING_JOBS

Source

View Text - Preformatted

SELECT ju.name, 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#)
      JOIN user$ ju ON (jo.owner# = ju.user# AND
        (jo.owner# = userenv('SCHEMAID')
         or jo.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               )
            )
         or /* user has system privileges */
           (exists (select null from v$enabledprivs
                 where priv_number  = -265 /* CREATE ANY JOB */
                )
            and jo.owner#!=0)
        )
      )
      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 lju.name, 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#)
      JOIN user$ lju ON (ljo.userid = lju.user# AND
        (ljo.userid = userenv('SCHEMAID')
         or (lj.program_oid is not null and
              lj.program_oid in
              (select loa.obj#
               from sys.objauth$ loa
               where grantee# in ( select kzsrorol
                                   from x$kzsro
                                 )
              )
            )
         or /* user has system privileges */
           (exists (select null from v$enabledprivs
                 where priv_number  = -265 /* CREATE ANY JOB */
                )
            and ljo.userid !=0)
        )
      )
      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 mu.name, 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')
        or (md.prgoid is not null and
            md.prgoid in
             (select loa.obj#
               from sys.objauth$ loa
               where grantee# in ( select kzsrorol
                                   from x$kzsro
                                 )
              )
            )
          or /* user has system privileges */
           (exists (select null from v$enabledprivs
                 where priv_number  = -265 /* CREATE ANY JOB */
                )
            and mr.userid !=0)
        ))
    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 JU.NAME
, 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#) JOIN USER$ JU ON (JO.OWNER# = JU.USER# AND (JO.OWNER# = USERENV('SCHEMAID') OR JO.OBJ# IN (SELECT OA.OBJ#
FROM SYS.OBJAUTH$ OA
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO ) ) OR /* USER HAS SYSTEM PRIVILEGES */ (EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER = -265 /* CREATE ANY JOB */ )
AND JO.OWNER#!=0) ) ) 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 LJU.NAME
, 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#) JOIN USER$ LJU ON (LJO.USERID = LJU.USER# AND (LJO.USERID = USERENV('SCHEMAID') OR (LJ.PROGRAM_OID IS NOT NULL AND LJ.PROGRAM_OID IN (SELECT LOA.OBJ#
FROM SYS.OBJAUTH$ LOA
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO ) ) ) OR /* USER HAS SYSTEM PRIVILEGES */ (EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER = -265 /* CREATE ANY JOB */ )
AND LJO.USERID !=0) ) ) 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 MU.NAME
, 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') OR (MD.PRGOID IS NOT NULL AND MD.PRGOID IN (SELECT LOA.OBJ#
FROM SYS.OBJAUTH$ LOA
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO ) ) ) OR /* USER HAS SYSTEM PRIVILEGES */ (EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER = -265 /* CREATE ANY JOB */ )
AND MR.USERID !=0) )) 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