VIEW: SYS.ALL_SCHEDULER_GROUP_MEMBERS

Source

View Text - Preformatted

SELECT wgm.owner, wgm.group_name,wgm.cred || wgm.mem_name
FROM
(SELECT u.name owner, o.name group_name, wg.member_oid2 member_oid2,
        decode(member_oid2, null,null,'"' || cmu.name || '"."' || cmo.name || '"@') cred,
        decode(wmu.name || '"' || substr(wmo.name,1,12), 'SYS"SCHED$_LOCAL', 'LOCAL',
          '"'  || wmu.name || '"."' || wmo.name || '"' )mem_name
FROM user$ u, obj$ o, scheduler$_window_group w, scheduler$_wingrp_member wg,
     user$ wmu, obj$ wmo, user$ cmu, obj$ cmo
WHERE w.obj# = wg.oid AND w.obj# = o.obj# AND o.owner# = u.user# AND
  wg.member_oid = wmo.obj# AND wmo.owner# = wmu.user# AND
  cmo.obj#(+) = wg.member_oid2 AND cmo.owner# = cmu.user#(+) AND
  (bitand(w.flags, 8+16) = 0 OR -- this is not a job or dest group
   (bitand(w.flags, 8+16) != 0 AND  -- this is a job or destination group
    (o.owner# = USERENV('SCHEMAID') OR -- user owns this group
     wg.oid IN (select oa1.obj# from sys.objauth$ oa1  -- has obj privs on group
                       where grantee# in (select kzsrorol from x$kzsro)) OR
     (EXISTS (select null from v$enabledprivs  -- has CREATE ANY JOB
                 where priv_number = -265) AND o.owner# <> 0))) ) ) wgm

View Text - HTML Formatted

SELECT WGM.OWNER
, WGM.GROUP_NAME
, WGM.CRED || WGM.MEM_NAME FROM (SELECT U.NAME OWNER
, O.NAME GROUP_NAME
, WG.MEMBER_OID2 MEMBER_OID2
, DECODE(MEMBER_OID2
, NULL
, NULL
, '"' || CMU.NAME || '"."' || CMO.NAME || '"@') CRED
, DECODE(WMU.NAME || '"' || SUBSTR(WMO.NAME
, 1
, 12)
, 'SYS"SCHED$_LOCAL'
, 'LOCAL'
, '"' || WMU.NAME || '"."' || WMO.NAME || '"' )MEM_NAME FROM USER$ U
, OBJ$ O
, SCHEDULER$_WINDOW_GROUP W
, SCHEDULER$_WINGRP_MEMBER WG
, USER$ WMU
, OBJ$ WMO
, USER$ CMU
, OBJ$ CMO WHERE W.OBJ# = WG.OID
AND W.OBJ# = O.OBJ#
AND O.OWNER# = U.USER# AND WG.MEMBER_OID = WMO.OBJ#
AND WMO.OWNER# = WMU.USER# AND CMO.OBJ#(+) = WG.MEMBER_OID2
AND CMO.OWNER# = CMU.USER#(+) AND (BITAND(W.FLAGS
, 8+16) = 0 OR -- THIS IS NOT A JOB OR DEST GROUP (BITAND(W.FLAGS
, 8+16) != 0
AND -- THIS IS A JOB OR DESTINATION GROUP (O.OWNER# = USERENV('SCHEMAID') OR -- USER OWNS THIS GROUP WG.OID IN (SELECT OA1.OBJ#
FROM SYS.OBJAUTH$ OA1 -- HAS OBJ PRIVS ON GROUP
WHERE GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)) OR (EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS -- HAS CREATE ANY JOB
WHERE PRIV_NUMBER = -265)
AND O.OWNER# <> 0))) ) ) WGM