I have 2 jobs that stopped running. They were marked as BROKEN. On *_SCHEDULER_JOB_LOG view it shows REASON="Job creator:[bda_user_xyz...] dropped".
Both jobs are setted as MAX_FAILURES = null
Does any one know if this is a bug? I can't find documentation of this behaivoir.
Regards
-----------------------------------------------------------------------------------------
NLSRTL 11.2.0.3.0 Production
Oracle Database 11g Enterprise Edition 11.2.0.3.0 64bit Production
PL/SQL 11.2.0.3.0 Production
TNS for Linux: 11.2.0.3.0 Production
-----------------------------------------------------------------------------------------
No. It's what happens when you drop the user who submits a job to run as someone else:
grant create session, create any job to jobu identified by jobu;
conn jobu/jobu
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'CHRIS.TESTJ',
job_type => 'PLSQL_BLOCK',
job_action => 'begin dbms_lock.sleep(10); end;',
number_of_arguments => 0,
start_date => NULL,
repeat_interval => 'FREQ=MINUTELY',
end_date => NULL,
enabled => TRUE,
auto_drop => FALSE,
comments => '');
END;
/
conn chris/chris
select owner, user_name, log_date, status, additional_info
from dba_scheduler_job_log
where job_name = 'TESTJ';
OWNER USER_NAME LOG_DATE STATUS ADDITIONAL_INFO
CHRIS <null> 18-JAN-2018 09.49.07 SUCCEEDED <null>
drop user jobu cascade;
/* wait a minute... */
select owner, user_name, log_date, status, additional_info
from dba_scheduler_job_log
where job_name = 'TESTJ';
OWNER USER_NAME LOG_DATE STATUS ADDITIONAL_INFO
CHRIS <null> 18-JAN-2018 09.49.07 SUCCEEDED <null>
CHRIS <null> 18-JAN-2018 09.49.57 <null> REASON="Job creator: JOBU dropped" As MOS 2121473.1 note says:
Jobs are executed with the privileges of the schema in which the job is created. The NLS environment of the job when it runs is that which was present at the time the job was created.
Jobs will be running under the schema that created the jobs, and not as the schema owner of each job. The reason is that the job creator is used as the login user when executing a job. In particular, auditing of the job will use the login user (job creator) in the audit entries for job actions. So this behavior handles auditing concerns related to job creation and subsequent execution.So if you've dropped the creator, you need to submit a new job.