The top activity graph in Oracle Enterprise Manager was reporting that certain activity was being performed by certain users at odd hours of the night. Upon researching, this activity was because the user in question was the creator of a scheduler job (even though the scheduler job was in a different schema). This has led to some amount of confusion, so we are attempting to have the Top Activity graph in OEM report that the activity is from the owner of the scheduler job instead of the job_creator.
Test Case
sqlplus kjohnston
begin
dbms_scheduler.create_job(job_name => 'UTILITIES.SCHEDULE_TEST', job_type => 'PLSQL_BLOCK',
job_action => 'begin for v_id in (select * from dba_tables) loop null; end loop; end;',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=1', auto_drop => FALSE, enabled => TRUE);
end;
When UTILITIES.SCHEDULE_TEST runs via the repeat_interval, the job is reported as running as the job_creator user.
SELECT sample_time, username, sql.sql_text
FROM v$active_session_history sess, v$sql sql, dba_users users
WHERE sess.user_id = users.user_id
AND sess.sql_id = sql.sql_id
AND upper(sql.sql_text) = 'SELECT * FROM DBA_TABLES'
ORDER BY sample_time desc;
SAMPLE_TIME USERNAME
14-DEC-15 14.47.52 KJOHNSTON
14-DEC-15 14.46.52 KJOHNSTON
However, when the job is run on-demand, the job is reported as having been run as the job owner.
exec dbms_scheduler.run_job('UTILITIES.SCHEDULE_TEST');
/
SELECT sample_time, username, sql.sql_text
FROM v$active_session_history sess, v$sql sql, dba_users users
WHERE sess.user_id = users.user_id
AND sess.sql_id = sql.sql_id
AND upper(sql.sql_text) = 'SELECT * FROM DBA_TABLES'
ORDER BY sample_time desc;
SAMPLE_TIME USERNAME
14-DEC-15 14.59.19 UTILITIES
My first attempt at a solution was to wrap the create_scheduler_job in a procedure owned by the owner of the scheduler job, then run the procedure to create the scheduler job... however, the job_creator is still KJOHNSTON (which would then still look like KJOHNSTON is running the job instead of the job owner: UTILITIES):
create or replace procedure utilities.create_scheduler_job as
begin
dbms_scheduler.create_job(job_name => 'UTILITIES.SCHEDULE_TEST', job_type => 'PLSQL_BLOCK',
job_action => 'begin for v_id in (select * from dba_tables) loop null; end loop; end;',
auto_drop => FALSE, enabled => TRUE);
end;
/
exec utilities.create_scheduler_job;
/
SELECT job_creator FROM dba_scheduler_jobs WHERE owner = 'UTILITIES' and job_name = 'SCHEDULE_TEST';
JOB_CREATOR
KJOHNSTON
I've considered 2 other potential solutions:
1) Proxy in as the job owner to create the scheduler job.
2) Create a generic SCHEDULER_JOB user that we login as in order to create the scheduler job.
Neither solution is particularly appealing to me as either solution requires more work per job than I would care to put into scheduler job creation.
Just to be clear, the jobs run without issue whether they are reported to have been run from the job_creator or the owner of the job, but this would help with the future analysis of top activity on our databases.
Has anyone else come across this issue and come up with a better/easier/more elegant solution?
select * from DBA_SCHEDULER_RUNNING_JOBS
helps in future analysis.