5 AS
6 (SELECT
7 e.LOG_ID, e.LOG_DATE, e.OWNER,
8 DECODE(instr(e.NAME,'"'),0, e.NAME,substr(e.NAME,1,instr(e.NAME,'"')-1)),
9 DECODE(instr(e.NAME,'"'),0,NULL,substr(e.NAME,instr(e.NAME,'"')+1)),
10 co.NAME, OPERATION, e.STATUS, e.USER_NAME, e.CLIENT_ID, e.GUID,
11 decode(e.credential, NULL, NULL,
12 substr(e.credential, 1, instr(e.credential, '"')-1)),
13 decode(e.credential, NULL, NULL,
14 substr(e.credential, instr(e.credential, '"')+1,
15 length(e.credential) - instr(e.credential, '"'))),
16 decode(bitand(e.flags, 1), 0, NULL,
17 substr(e.destination, 1, instr(e.destination, '"')-1)),
18 decode(bitand(e.flags, 1), 0, e.destination,
19 substr(e.destination, instr(e.destination, '"')+1,
20 length(e.destination) - instr(e.destination, '"'))),
21 e.ADDITIONAL_INFO
22 FROM scheduler$_event_log e, obj$ co
23 WHERE e.type# = 66 and e.dbid is null and e.class_id = co.obj#(+)
24 AND ( e.owner = SYS_CONTEXT('USERENV','CURRENT_USER')
25 or /* user has object privileges */
26 ( select jo.obj# from obj$ jo, user$ ju where
27 DECODE(instr(e.NAME,'"'),0, e.NAME,substr(e.NAME,1,instr(e.NAME,'"')-1)) = jo.name
28 and e.owner = ju.name and jo.owner# = ju.user#
29 and jo.subname is null and jo.type# = 66
30 ) in
31 ( select oa.obj#
32 from sys.objauth$ oa
33 where grantee# in ( select kzsrorol from x$kzsro )
34 )
35 or /* user has system privileges */
36 (exists ( select null from v$enabledprivs
37 where priv_number = -265 /* CREATE ANY JOB */
38 )
39 and e.owner!='SYS')
40 )
41 )
42 /
View created.
SQL>
SQL> CREATE OR REPLACE VIEW all_scheduler_job_run_details
2 ( LOG_ID, LOG_DATE, OWNER, JOB_NAME, JOB_SUBNAME, STATUS, ERROR#, REQ_START_DATE,
3 ACTUAL_START_DATE, RUN_DURATION, INSTANCE_ID, SESSION_ID, SLAVE_PID,
4 CPU_USED, CREDENTIAL_OWNER, CREDENTIAL_NAME, DESTINATION_OWNER,
5 DESTINATION, ADDITIONAL_INFO)
6 AS
7 (SELECT
8 j.LOG_ID, j.LOG_DATE, e.OWNER,
9 DECODE(instr(e.NAME,'"'),0, e.NAME,substr(e.NAME,1,instr(e.NAME,'"')-1)),
10 DECODE(instr(e.NAME,'"'),0,NULL,substr(e.NAME,instr(e.NAME,'"')+1)),
11 e.STATUS, j.ERROR#, j.REQ_START_DATE, j.START_DATE, j.RUN_DURATION,
12 j.INSTANCE_ID, j.SESSION_ID, j.SLAVE_PID, j.CPU_USED,
13 decode(e.credential, NULL, NULL,
14 substr(e.credential, 1, instr(e.credential, '"')-1)),
15 decode(e.credential, NULL, NULL,
16 substr(e.credential, instr(e.credential, '"')+1,
17 length(e.credential) - instr(e.credential, '"'))),
18 decode(bitand(e.flags, 1), 0, NULL,
19 substr(e.destination, 1, instr(e.destination, '"')-1)),
20 decode(bitand(e.flags, 1), 0, e.destination,
21 substr(e.destination, instr(e.destination, '"')+1,
22 length(e.destination) - instr(e.destination, '"'))),
23 j.ADDITIONAL_INFO
24 FROM scheduler$_job_run_details j, scheduler$_event_log e
25 WHERE j.log_id = e.log_id
26 AND e.type# = 66 and e.dbid is null
27 AND ( e.owner = SYS_CONTEXT('USERENV','CURRENT_USER')
28 or /* user has object privileges */
29 ( select jo.obj# from obj$ jo, user$ ju where
30 DECODE(instr(e.NAME,'"'),0, e.NAME,substr(e.NAME,1,instr(e.NAME,'"')-1)) = jo.name
31 and e.owner = ju.name and jo.owner# = ju.user#
32 and jo.subname is null and jo.type# = 66
33 ) in
34 ( select oa.obj#
35 from sys.objauth$ oa
36 where grantee# in ( select kzsrorol from x$kzsro )
37 )
38 or /* user has system privileges */
39 (exists ( select nu