manually running a scheduled job. LAST_RUN_DATE is not updating

Question

I have a job on the database which runs nightly at 1am using the DBMS scheduler.

I also want to be able to run the job manually whenever I was.

I do this by running

execute dbms_scheduler.run_job('JOB_NAME');

This all works fine and the job does run, however when i look up the job on DBA_SCHEDULER_JOBS the LAST_RUN_DATE column still only holds the date that the job was last run from the scheduler (at 1am) and not when i manually ran it. Does this make sense to anyone. I want to be able to get the last time the job was run so I can show the user running it.

This is the query where I'm looking up the LAST_RUN_DATE,

SELECT LAST_START_DATE 
   FROM DBA_SCHEDULER_JOBS 
WHERE job_name='JOB_NAME';

My work around is to use this to get the last date but it doesn't seem right:

select log_date, job_name, status, run_duration
  from dba_scheduler_job_run_details 
where job_name='JOB_NAME'

"it doesn't seem right": please elaborate. – Kris Johnston May 03 '17 at 18:46 — Kris Johnston, May 03 '17 at 18:46

score 6 · Accepted Answer · answered May 03 '17 at 18:45

The optional parameter use_current_session on dbms_scheduler.run_job defaults to TRUE when it is omitted. Setting this parameter to FALSE will update last_start_date:

execute dbms_scheduler.run_job('JOB_NAME', use_current_session => false);

However, you may have other side effects as a result by setting this parameter. Refer to the Oracle docs for your specific Oracle version for more details.

12.1 docs (emphasis mine):

use_current_session:

This specifies whether or not the job run should occur in the same session that the procedure was invoked from.The job always runs as the job owner, in the job owner's schema, unless it has credential specified, then the job runs using the user named in the credential.

When use_current_session is set to TRUE:

You can test a job and see any possible errors on the command line.

state, run_count, last_start_date, last_run_duration, and failure_count of *_scheduler_jobs are not updated.

RUN_JOB can be run in parallel with a regularly scheduled job run.

When use_current_session is set to FALSE:

You need to check the job log to find error information. All relevant fields in *_scheduler_jobs are updated. RUN_JOB fails if a regularly scheduled job is running.

Thank you. This is exactly what I was looking for. – kev670 May 03 '17 at 19:46 — kev670, May 03 '17 at 19:46

manually running a scheduled job. LAST_RUN_DATE is not updating

1 Answers1