Dbms_Scheduler.Create_Job

The dbms_scheduler.create_job procedure is used to create scheduler jobs.  Here is an example of it being used to call a stored procedure:

begin
dbms_scheduler.create_job (
   job_name           =>  'run_load_sales',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'system.load_sales',
   start_date         =>  '01-MAR-2010 03:00:00 AM',
   repeat_interval    =>  'FREQ=DAILY',
   enabled            =>  TRUE);
END;
/

This creates the job named run_load_sales which executes the stored procedure system.load_sales.  The start date is configured as 3:00 AM on March 1 and the frequency is set to daily.  Normally, jobs are disabled when they are created, but the line beginning enabled automatically enables this job at creation.

See these important notes on when an old dbms_scheduler job is still running at job execution time.  Also, there are tips about this in Dr. Hall's recommended book "Oracle Job Scheduling"

NOTE: Scheduled jobs are now treated like other database objects and need to have unique names within a schema.  For example, you cannot create a job named load_sales if you already have a procedure by that name.

Each time this job is executed, the scheduler evaluates the repeat_intervalto determine the next time this job should be run.  In this case, at 3:00 AM on March 1st it evaluates freq=daily and determines that the job should be run daily and, with no other criteria, schedules the next running of the job at 3:00 AM on March 2nd.

There are a large number of expressions that can be used to define the repeat interval for a job.  Here are a few examples:

repeat_interval	Description
freq=hourly	Run every hour
freq=daily; byhour=3	Run at 3 am every day
freq=daily; byhour=8,20	Run at 8 am and 8 pm every day
freq=monthly; bymonthday=1	Run on the first day of every month
freq=monthly; bymonthday=-1	Run on the last day of every month
freq=yearly; bymonth=sep; bymonthday=20;	Run yearly on September 20th

Table 19.3:  repeat_interval Expressions

There are almost unlimited permutations possible for the repeat_interval value.  These are only a few possibilities but should get you started.

Creating a Job That Calls an Executable

The job type executable allows you to create jobs which execute a command or script at the operating system level.  The syntax is similar to creating other jobs, but the job type is set to executable and the job action should include the full path to the command or script to be executed.

begin
dbms_scheduler.create_job (
   job_name           =>  'migrate_files',
   job_type           =>  'executable',
   job_action         =>  '/home/oracle/bin/migrate_files.sh',
   start_date         =>  '01-mar-2010 07:00:00 am',
   repeat_interval    =>  'freq=daily',
   enabled            =>  true);
end;
/

When the date comes up to execute this job, Oracle executes the migrate_files.sh script as the user who the database is running under (typically oracle.)  That user must already have execute privileges on the script or command to be run in order for the job to succeed.

Changing a Job

You can change anything about a scheduled job, except its name, using the dbms_scheduler.set_attribute procedure.  The job name is given, followed by the attribute that you wish to change, and finally, the new value for that attribute.

begin
dbms_scheduler.set_attribute (
name               =>  'run_load_sales',
attribute          =>  'repeat_interval',
value              =>  'freq=daily; byhour=3');
end;
/

A job can be changed while it is running, but the changes will not take effect until the next run of the job.

Running a Job Manually

If you want to run a job immediately, call the dbms_scheduler.run_job procedure.

begin
dbms_scheduler.run_job (job_name => 'run_load_sales');
end;
/

This causes the named job to be run immediately.

Stopping Running Jobs

Running jobs can be stopped using the dbms_scheduler.stop_jobprocedure.

begin
dbms_scheduler.stop_job (job_name => 'run_load_sales');
end;
/

This only stops the running job and does not affect future running of this job.

Disabling and Enabling Jobs

The dbms_schedulerpackage includes the procedures disable and enable to disable and enable jobs.  When a job is disabled, it will not be run.

begin
dbms_scheduler.disable (job_name => 'run_load_sales');
end;
/

If the job is running when the disable procedure is called, you get an error.  You can stop the running job as shown in the last example, or you can add force => true to the disable statement.

To re-enable a job which has been disabled, use the enable procedure.

begin
dbms_scheduler.enable (job_name => 'run_load_sales');
end;
/

The job will now be run based on its original schedule.  Multiple jobs can be disabled or enabled at the same time by separating their names with a comma.

Dropping Jobs

To permanently drop a job, call the procedure dbms_scheduler.drop_job.  As with disabling and enabling jobs, multiple jobs can be specified by separating them with commas.

begin
dbms_scheduler.drop_job ('run_load_sales');
end;
/

If a job is running when you try to drop it, you get an error.  You can stop the job, then drop it or set the force parameter to true.  Setting force to true causes the running job to be stopped, and then the job is dropped from the scheduler.

Source: R&D/Internet