Skip to Main Content
  • Questions

  • When Dba_Scheduler_Jobs.Start_Date is not specified, where does Oracle store Job Enabling time?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Pankaj.

Asked: January 28, 2021 - 11:48 am UTC

Last updated: February 01, 2021 - 9:24 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hello,

I've a question on DBMS_Scheduler functionality:

When I create a scheduler job via DBMS_SCHEDULER.CREATE_JOB proc and I don't specify Start_Date value, then job is scheduled for execution as soon as it is enabled which is correct. I want to know where does Oracle store the Job enabling time because same enabling time can be seen in dba_scheduler_job_run_details.Req_Start_Date when job finishes.

Basically I want to enabling time for a job which is in SCHEDULED state and Start_date value for that job is NULL.

Thanks for sharing knowledge!

and Chris said...

It seems to me you're searching for something that doesn't exist...

Jobs are in the SCHEDULED state if their start date is in the future. If you make it an immediate job (start date is null), it's not scheduled!

The job will be DISABLED until you you enable it; this is the req_start_date for it at which point it runs:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
            job_name => 'TEST_JOB',
            job_type => 'PLSQL_BLOCK',
            job_action => 'begin null; end;',
            start_date => null,
            end_date => null,
            enabled => false,
            auto_drop => false
    );
 
    DBMS_SCHEDULER.SET_ATTRIBUTE( 
         name => 'TEST_JOB', 
         attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_RUNS
    );
    
END;
/

select job_name, state 
from   user_scheduler_jobs;

JOB_NAME   STATE      
TEST_JOB   DISABLED    

exec dbms_scheduler.enable  ( 'TEST_JOB' );

select job_name, state 
from   user_scheduler_jobs;

JOB_NAME   STATE       
TEST_JOB   SUCCEEDED    

select status, req_start_date, actual_start_date
from   user_scheduler_job_run_details
where  job_name = 'TEST_JOB';

STATUS      REQ_START_DATE             ACTUAL_START_DATE          
SUCCEEDED   29-JAN-2021 09.49.38 +00   29-JAN-2021 09.49.38 +00


So what exactly is the problem you're trying to resolve here?

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Additional info

Pankaj, January 29, 2021 - 12:34 pm UTC

Thanks for the detailed response.

When you did 
 exec dbms_scheduler.enable  ( 'TEST_JOB' );
then job status got changed from DISABLED to SCHEDULED. Now, TEST_JOB is ready to be picked up by scheduler but consider a case when scheduler is already busy in executing already existing jobs and there are no free slots available. In this case, TEST_JOB is going to wait for a running job to finish.
At this point, when TEST_JOB is sitting in SCHEDULED state, how do i find job enabling time (or req_start_date, as data in user_scheduler_job_run_details is not yet populated).

In other words, from which table/view this user_scheduler_job_run_details.req_start_date value coming from?
Chris Saxon
February 01, 2021 - 9:23 am UTC

Got it; if you set the log level for the job to DBMS_SCHEDULER.LOGGING_RUNS, the database will record the enabling time:

alter system set job_queue_processes = 1;

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
            job_name => 'INFINIT_JOB',
            job_type => 'PLSQL_BLOCK',
            job_action => 'begin loop null; end loop; end;',
            start_date => null,
            end_date => null,
            enabled => false,
            auto_drop => false
    );
 
    DBMS_SCHEDULER.SET_ATTRIBUTE( 
         name => 'INFINIT_JOB', 
         attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_RUNS
    );
    dbms_scheduler.enable  ( 'INFINIT_JOB' );
    
END;
/

select job_name 
from   user_scheduler_running_jobs;

JOB_NAME      
INFINIT_JOB  

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
            job_name => 'TEST_JOB',
            job_type => 'PLSQL_BLOCK',
            job_action => 'begin null; end;',
            start_date => null,
            end_date => null,
            enabled => false,
            auto_drop => false
    );
 
    DBMS_SCHEDULER.SET_ATTRIBUTE( 
         name => 'TEST_JOB', 
         attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_FULL
    );
    
END;
/

select job_name, state, next_run_date
from   user_scheduler_jobs;

JOB_NAME      STATE       NEXT_RUN_DATE   
INFINIT_JOB   RUNNING     <null>           
TEST_JOB      SCHEDULED   <null>    

exec dbms_scheduler.enable  ( 'TEST_JOB' );

select job_name, operation, log_date 
from   user_scheduler_job_log
where  job_name = 'TEST_JOB'
and    log_date > trunc ( sysdate );

JOB_NAME   OPERATION   LOG_DATE                   
TEST_JOB   ENABLE      01-FEB-2021 09.20.01 +00

Additional info

A reader, February 01, 2021 - 7:15 am UTC 


Check NEXT_RUN_DATE in dba_scheduler_jobs.

You may also want to increase job_queue_processes to have multiple jobs running at the same time.

Hope it helps.

Cheers

Chris Saxon
February 01, 2021 - 9:24 am UTC

NEXT_RUN_DATE is not populated; see example above.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database