DBMS Scheduler :: Oracle World

Post Views: 2,789

Oracle provides a tool for scheduling business tasks and procedures. It would be a bit boring to run tasks every day manually. Personally, it bothers me to run something manually even monthly or quarterly 🙂 Most programmers are just lazy by nature. With dbms_scheduler, you can make your life easier 🙂 There are many parameters you can use and if you want to master them all, I will refer you to the Oracle docs. Those I will demonstrate will cover about 99% of your requirements.

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'SALES_DATA_LOAD',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN my_schema.procedure_name; END;',
    start_date      => '1-DEC-18 06.00.00 AM +08:00',  
    repeat_interval => 'freq=daily; interval=1;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Description of the job comes here'
  );
END;
/

This is the basic template for job creation and I suppose most of them are self-explanatory. I will delve into a few only to, especially those where you have many choices to go with.

JOB_NAME – this is your custom name for a job; choose wisely because it should define the process
JOB_TYPE – possible values are
- PLSQL_BLOCK – used for anonymous blocks
- STORED_PROCEDURE – used for PL/SQL, Java or C procedure. Functions with a return value are not allowed
- EXECUTABLE – used for an external executable file; will run outside of the database
- CHAIN – used for a chain. I will focus on chains in my next article Oracle scheduler – chains.
- EXTERNAL_SCRIPT – used for EXE (Windows) or SH (Linux) files
- SQL_SCRIPT – used for SQL*Plus scripts; the script must contain credentials
- BACKUP_SCRIPT – used for RMAN
JOB_ACTION – this is an inline code depends on the selected JOB_TYPE (i.e. PL/SQL, executable, chain, …)
START_DATE – when the job should start; you can use either SYSTIMESTAMP or a specific date (as it is in the example above)
REPEAT_INTERVAL – schedule defined for that job; this might be very flexible and allows you to set up very specific runs according to your business logic
- Possible values:
  - FREQ – defines the frequency of the schedule (Yearly, Monthly, Daily, Hourly, Minutely, Secondly)
  - INTERVAL – defines how often the recurrence will repeat (1-99)
  - BYMONTH – defines in which month(-s) should the job be executed (use either numbers or three-letter abbreviations)
  - BYDAY – defines in which day(-s) of the week should the job be executed (use either numbers or three-letter abbreviations)
  - BYWEEKNO – defines in which week(-s) should the job be executed (use numbers 1-52 or 53)
  - BYYEARDAY – defines in which day(-s) of the year should the job be executed (use numbers 1-366)
  - BYHOUR – defines in which hour(-s) should the job be executed (use numbers 1-24)
  - BYMINUTE – defines in which minute(-s) should the job be executed (use number 1-60)
  - and many more …
END_DATE – if you want the job to be scheduled for a specific period only, set this end date (same format as for the START_DATE). If you do not want it to end, set it to NULL
ENABLED – if you want just to create a schedule but do not run it yet, set this parameter to FALSE, otherwise TRUE
COMMENTS – this is a very important field and should be filled all the time (even though it is not mandatory). This is a description of your job.

Now, check some more examples of how you can create a job

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'SALES_DATA_LOAD',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'my_schema.proc_sales_data_load',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; interval=1; byday=MON,WED,FRI; byhour=7,12,15,20',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Some description'
  );
END;
/


BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'SALES_DATA_LOAD',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'my_schema.proc_sales_data_load',
    start_date      => '1-APR-19 06.00.00 AM +08:00'
    repeat_interval => 'freq=hourly; interval=6; byday=MON;', -- every 6 hours on Monday only
    end_date        => '1-MAY-10 06.00.00 AM +08:00',
    enabled         => FALSE,
    comments        => 'Some description'
  );
END;
/


-- or an example of a job to update statistics
BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'UPDATE_SALES_STATS',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN dbms_stats.gather_schema_stats(OWNNAME => 'SALES_DATA', CASCADE => 'TRUE') END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; interval=1; byhour=2',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Update SALES statistics'
  );
END;
/

Now, when we have a job created, we have to be able to modify it in case of need. There are several actions we can do. Let’s have a look at them

Alter job

You can modify all attributes except the job name. If you want to rename the job, you have to recreate it again. All changes are done with the SET_ATTRIBUTE function from the DBMS_SCHEDULER package.

--change job_action
BEGIN    
    DBMS_SCHEDULER.set_attribute (
        name        => 'MY_JOB_NAME',
        attribute   => 'job_action',
        value       => 'BEGIN
                           my_schema.new_procedure_name;     
                        END;'
    );
END;
/

-- change the start time  
BEGIN   
    DBMS_SCHEDULER.SET_ATTRIBUTE(  
       name         => 'MY_JOB_NAME',  
       attribute    => 'start_date',  
       value        => '15-AUG-15 06.05.00 AM +08:00' -- new value; format expl: DD-MON-YY HH.MI.SS AM +08:00(=TZ)   
    );  
END;
/

-- change repeat interval  
BEGIN 
    DBMS_SCHEDULER.SET_ATTRIBUTE(  
       name => 'MY_JOB_NAME',  
       attribute => 'repeat_interval',  
       value => 'freq=MINUTELY;interval=2' -- new interval here 
    ); 
END;
/

Run job

Not only the job runs at a specified frequency defined by the scheduler but you can also run it manually when needed.

BEGIN
  DBMS_SCHEDULER.run_job (job_name => 'MY_JOB_NAME');
END;
/

-- some folks prefer the EXEC version ... I rather stick with the BEGIN / END
EXEC DBMS_SCHEDULER.run_job (job_name => 'MY_JOB_NAME');

Drop job

If you do not need a job anymore, clean the mess after yourself 🙂 Always keep your database clean and free of unwanted/unused objects. This applies to all objects in a database not only to jobs.

BEGIN
 DBMS_SCHEDULER.drop_job(job_name => 'MY_JOB_NAME');
END;
/

Stop job

If there is something wrong or you simply want to stop a running job, you can do so by executing the command below.

-- stop one job
BEGIN
 DBMS_SCHEDULER.stop_job(job_name => 'MY_JOB_NAME');
END;
/

-- stop multiple jobs 
BEGIN
 DBMS_SCHEDULER.stop_job(job_name => 'MY_JOB_NAME, ANOTHER_JOB_NAME, JOB_NAME_XY');  
END;
/

Disable job

If you don’t want the job to be executed (i.e. bad performance and you need to tune it first) you can disable it so it won’t run until enabled again.

BEGIN
  DBMS_SCHEDULER.disable (name => 'MY_JOB_NAME');
END;
/

Enable job

Once your job is ready to be enabled (for whatever reason). go ahead 🙂

BEGIN
  DBMS_SCHEDULER.enable (name => 'MY_JOB_NAME');
END;
/

Now, you can manage scheduler jobs on a sufficient level and you should be able to create, drop, alter, and to do many operations with jobs. It’s time to show you where you can see all jobs stored, how you can see whether the job is running, how to see the history of job run details and many more; buckle up 🙂

Schedule jobs (the most commonly used)

ALL_SCHEDULER_JOBS – shows all jobs, enabled and disabled with some statistics
ALL_SCHEDULER_JOB_ARGS – show all arguments defined for all jobs
ALL_SCHEDULER_JOB_CLASSES – show all classes for all jobs
ALL_SCHEDULER_JOB_LOG – shows all state changes made to jobs
ALL_SCHEDULER_RUNNING_JOBS – shows all running jobs and details about the run
ALL_SCHEDULER_JOB_RUN_DETAILS – shows all completed/finished (successful or failed) job runs

Chains

ALL_SCHEDULER_CHAINS – shows info about all chains
ALL_SCHEDULER_RUNNING_CHAINS – shows info about all running chains
ALL_SCHEDULER_CHAIN_STEPS – shows all steps for all chains
ALL_SCHEDULER_CHAIN_RULES – shows all rules for all chains

Windows

ALL_SCHEDULER_WINDOWS – show all windows
ALL_SCHEDULER_WINDOW_GROUPS – shows all window groups
ALL_SCHEDULER_WINGROUP_MEMBERS – shows the members of all window groups, one row for each group member
ALL_SCHEDULER_WINDOW_LOG – shows all state changes made to windows
ALL_SCHEDULER_WINDOW_DETAILS – show all completed window runs

Misc

ALL_SCHEDULER_SCHEDULES – shows all schedules
ALL_SCHEDULER_PROGRAMS – shows all programs
ALL_SCHEDULER_PROGRAM_ARGS – show all arguments defined for all programs together with default values (if exist)
ALL_SCHEDULER_GLOBAL_ATTRIBUTE – show all global attributes set for the scheduler

You can replace ALL_ with DBA_ or USER_ – depends on your privileges. You can find out the difference in the article DMBS and other relational models.