Need a query to find the current status of the last run of a particular Oracle job

Question

I want to have a query that I can run to find the status of the last run of a particular Oracle job. Consider the procedure below that is then called using the dbms_job.submit command.

CREATE OR REPLACE PROCEDURE USER1.fake_do_mv_refresh (
        p_sleep_secs int,
        p_user    VARCHAR2,
        p_cm_r_pm VARCHAR2 DEFAULT 'CM'
    )
AS
fake_error EXCEPTION;
BEGIN
  DBMS_LOCK.SLEEP(p_sleep_secs);
    raise fake_error;
    --INSERT INTO FDS_APPS.MSG VALUES (p_cm_r_pm, current_timestamp);
    --commit;
END fake_do_mv_refresh;
/
---------------------------------------------------
--Call the above procedure  with dbms_job.submit
---------------------------------------------------

DECLARE
l_jobid int := -1;
l_what varchar2(1000) := 'ok';
p_user varchar(10) := 'USER1';
p_cm_r_pm varchar(2) := 'CM';
p_sleep_secs int := 60;
BEGIN
  
    l_what :=  'USER1.fake_Do_mv_refresh(' || p_sleep_secs || ',''' || p_user  || ''',''' || p_cm_r_pm || ''');';    
    dbms_output.put_line(l_what);

    dbms_job.submit(job => l_jobid, what => l_what);
    dbms_output.put_line(l_jobid);
 
    COMMIT;
 
END;

-- Try to Find the job

select * FROM DBA_SCHEDULER_RUNNING_JOBS -- Shows the job while it is running 
select * FROM DBA_JOBS; --The "WHAT" column is useful in locating a particular job but this view, too, only seems to returns the job while it is running!
select * FROM all_scheduler_job_run_details order by log_date desc; --This view shows the job but with not column that contains identifiable info like the WHAT column above

I was surprised that the all_scheduler_job_run_details view didn't give me more information to identify the submitted job and that what it did provide, I was unable to tie to anotehr table, like the DBA_JOBS table, which did have a useful WHAT column but the latter view seemed only to show running jobs...

Any suggestions on how to write a SQL to get the status of the last submitted job of this type?

Answer 1

When you create a legacy job with dbms_job instead of using the newer, far better replacement dbms_scheduler, Oracle will actually create a new scheduler job (with the new engine) which you control via the legacy dbms_job as a kind of legacy wrapper. The old engine isn't used anymore at all. The actual job name used in the scheduler (dba_scheduler_jobs) is system-named using the legacy job (from dba_jobs).

SELECT *
  FROM dba_jobs j, 
       dba_scheduler_jobs s
 WHERE s.job_name = 'DBMS_JOB$_'||j.job
   AND j.schema_user = s.owner

So for history:

SELECT *
  FROM dba_jobs j, 
       dba_scheduler_job_run_details s
 WHERE s.job_name = 'DBMS_JOB$_'||j.job
   AND j.schema_user = s.owner

I suggest not using dbms_job for anything anymore. Create your jobs with dbms_scheduler so that you can give them good names and work them with more easily and with all the flexibility that the newer engine provides.