The Scheduler


Starting with 10g offers a brand new job-scheduling facility, known as The Scheduler, controlled via the new package dbms_scheduler.
This package replaces the dbms_job (but that one is still available). The new scheduler offers more functionality over the dbms_job package. The Scheduler enables you to execute a variety of stored code (such as PL/SQL), a native binary executable, and OS scripts (so you can get rid of cron jobs).

Oracle provides two different interfaces into The Scheduler. The first is the dbms_scheduler package and the second is through the Oracle Enterprise Manager (OEM).

As we mentioned, there are several components:
Programs
Schedules (with Interval Examples)
Jobs
Job Classes
Windows
Windows Groups
Enable, Disable and atributes
Converting DB jobs to Scheduler
Full Example to perform Backups
Recreate (or Copy) Oracle Scheduler Jobs
More Resources for 10gr2 and 11g

Managing Programs
The scheduler allows you to optionally create programs which hold metadata about a task(such as the arguments to be passed to it and the type of program that is being run), but no schedule information. A program may related to a PL/SQL block, a stored procedure or an OS executable file. Programs can be stored in program libraries, which allows for easy reuse of program code by other users. Each program, when scheduled, is assigned to a job.
Creating a program is the optional first step when creating a scheduled operation. This operation may actually take four steps:
1. Create the program itself.
2. Define the program arguments.
3. Create the job.
4. Define job arguments.
The following sections explain each of these steps in turn.

1.Creating the Program
To create a program, so that you can schedule it, you use the PL/SQL-supplied procedure dbms_scheduler.create_program.
To use this package in your own schema, you must have the create job privilege. To use it to create jobs in other schemas, you need the create any job privilege. If you are going to define an external job, then you need the create external job privilege.
By default, a program is created in a disabled state (which can be overridden by setting the enabled parameter of the create_program procedure to TRUE).

First, let’s look at the definition of the dbms_scheduler.create_program procedure:
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name IN VARCHAR2,
program_type IN VARCHAR2,
program_action IN VARCHAR2,
number_of_arguments IN PLS_INTEGER DEFAULT 0,
enabled IN BOOLEAN DEFAULT FALSE,
comments IN VARCHAR2 DEFAULT NULL);

Let’s look at a description of the parameters for the create_program procedure:
Parameter Name            Description
program_name               Identifies the name of the program. This is an internally assigned name, which represents the program_action that will be executed.
program_type                 Identifies the type of executable being scheduled. Currently, the following are valid values: PLSQL_BLOCK, STORED_PROCEDURE, and EXECUTABLE.
program_action              Indicates the procedure, executable name, or PL/SQL anonymous block associated with the program.
number_of_arguments  Identifies the number of arguments required for the program (ignored if program_type is PLSQL_BLOCK).
Enabled                           Indicates whether the program should be enabled when created.
Comments                      Allows freeform comments describing the program or what it does.

Here is an example showing the creation of different type of programs:
BEGIN
  -- Creating a PL/SQL Block.
  DBMS_SCHEDULER.create_program (
    program_name   => 'TEST_MY_PLSQL_BLOCK_PROG',
    program_type   => 'PLSQL_BLOCK',
    program_action => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
    enabled        => TRUE,
    comments       => 'Program to gather SCOTT''s statistics using a PL/SQL block.');

  -- Creating a Shell Script.
  DBMS_SCHEDULER.create_program (
    program_name        => 'TEST_EXECUTABLE_PROG',
    program_type        => 'EXECUTABLE',
    program_action      => '/u01/app/oracle/dba/gather_scott_stats.sh',
    number_of_arguments => 0,
    enabled             => TRUE,
    comments            => 'Program to gather SCOTT''s statistics us a shell script.');


  -- Creating a Stored Procedure with Arguments.
  DBMS_SCHEDULER.create_program (
    program_name        => 'TEST_STORED_PROCEDURE_PROG',
    program_type        => 'STORED_PROCEDURE',
    program_action      => 'DBMS_STATS.gather_schema_stats',
    number_of_arguments => 1,
    enabled             => FALSE,
    comments            => 'Program to gather SCOTT''s statistics using a stored procedure.');

  -- Define Arguments for the previously created Program
  DBMS_SCHEDULER.define_program_argument (
    program_name      => 'TEST_STORED_PROCEDURE_PROG',
    argument_name     => 'ownname',
    argument_position => 1,
    argument_type     => 'VARCHAR2',
    default_value     => 'SCOTT');

END;
/

Reviewing your jobs:
SELECT program_name, enabled FROM user_scheduler_programs;
PROGRAM_NAME                   ENABL
------------------------------ -----
TEST_EXECUTABLE_PROG           TRUE
TEST_MY_PLSQL_BLOCK_PROG       TRUE
TEST_STORED_PROCEDURE_PROG     FALSE

You can enable one of the program as follows:
execute DBMS_SCHEDULER.ENABLE('TEST_MY_PLSQL_BLOCK_PROG');

You can disable one of the program as follows:
execute DBMS_SCHEDULER.DISABLE('TEST_MY_PLSQL_BLOCK_PROG');

Note that Oracle does not check for the existence of the program when the create_program procedure is executed. Thus, you can create your program even if the underlying executable doesn't exist.

Another Example calling a stored procedure:
begin
DBMS_SCHEDULER.create_program (
    program_name        => 'CALL_MAINTENANCE_DAILY',
    program_type        => 'STORED_PROCEDURE',
    program_action      => 'SCHEMA.DB_MAINTENANCE_DAILY',
    number_of_arguments => 1,
    enabled             => FALSE,
    comments            => 'Program to Clean WF Data at the end of the Date');
end;
/

You can drop a program with the dbms_scheduler.drop_program procedure, as shown in this example:
Exec dbms_scheduler.drop_program('CALL_MAINTENANCE_DAILY');


Schedules
Schedules optionally define the start time, end time and interval related to a job. Schedules are created using the CREATE_SCHEDULE procedure.
First, let’s look at the definition of the dbms_scheduler.CREATE_SCHEDULE procedure:
DBMS_SCHEDULER.CREATE_SCHEDULE (
   schedule_name          IN VARCHAR2,
   start_date             IN TIMESTAMP WITH TIMEZONE DEFAULT NULL,
   repeat_interval        IN VARCHAR2,
   end_date               IN TIMESTAMP WITH TIMEZONE DEFAULT NULL,
   comments               IN VARCHAR2 DEFAULT NULL);


Let’s look at a description of the parameters for the create_schedule procedure:
Parameter Name            Description
schedule_name               Identifies the name of the schedule.
start_date                        Specifies the first date on which this schedule becomes valid. If start_date is specified in the past and no value for repeat_interval is specified, the schedule is invalid. For a repeating job or window, start_date can be derived from the repeat_interval, if it is not specified.
repeat_interval               Specifies how often the schedule should repeat. It is expressed using a calendar expression, check the table below for examples.
end_date                          The date after which jobs will not run and windows will not open. A non-repeating schedule that has no end_date will be valid forever.
Comments                        Allows a comment describing the scheduler.

Calendar Syntax
Values for repeat_interval
Name Description

freq

This specifies the type of recurrence. It must be specified. The possible values are YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY.

interval

This specifies a positive integer representing how often the recurrence repeats. The default is 1, which means every second for secondly, every day for daily, and so on. The maximum value is 999.

bymonth

This specifies which month or months you want the job to execute in. You can use numbers such as 1 for January and 3 for March, as well as three-letter abbreviations such as FEB for February and JUL for July.

byweekno

This specifies the week of the year as a number. It follows ISO-8601, which defines the week as starting with Monday and ending with Sunday; and the first week of a year as the first week, which is mostly within the Gregorian year. That last definition is equivalent to the following two variants: the week that contains the first Thursday of the Gregorian year; and the week containing January 4th.

The ISO-8601 week numbers are integers from 1 to 52 or 53; parts of week 1 may be in the previous calendar year; parts of week 52 may be in the following calendar year; and if a year has a week 53, parts of it must be in the following calendar year.

As an example, in the year 1998 the ISO week 1 began on Monday December 29th, 1997; and the last ISO week (week 53) ended on Sunday January 3rd, 1999. So December 29th, 1997, is in the ISO week 1998-01; and January 1st, 1999, is in the ISO week 1998-53.

byweekno is only valid for YEARLY.

Examples of invalid specifications are "FREQ=YEARLY; BYWEEKNO=1; BYMONTH=12" and "FREQ=YEARLY;BYWEEKNO=53;BYMONTH=1".

byyearday

This specifies the day of the year as a number. Valid values are 1 to 366. An example is 69, which is March 10 (31 for January, 28 for February, and 10 for March). 69 evaluates to March 10 for non-leap years and March 9 in leap years. -2 will always evaluate to December 30th independent of whether it is a leap year.

bymonthday

This specifies the day of the month as a number. Valid values are 1 to 31. An example is 10, which means the 10th day of the selected month. You can use the minus sign (-) to count backward from the last day, so, for example, BYMONTHDAY=-1 means the last day of the month and BYMONTHDAY=-2 means the next to last day of the month.

byday

This specifies the day of the week from Monday to Sunday in the form MON, TUE, and so on. Using numbers, you can specify the 26th Friday of the year, if using a YEARLY frequency, or the 4th THU of the month, using a MONTHLY frequency. Using the minus sign, you can say the second to last Friday of the month. For example, -1 FRI is the last Friday of the month.

byhour

This specifies the hour on which the job is to run. Valid values are 0 to 23. As an example, 10 means 10AM.

byminute

This specifies the minute on which the job is to run. Valid values are 0 to 59. As an example, 45 means 45 minutes past the chosen hour.

bysecond

This specifies the second on which the job is to run. Valid values are 0 to 59. As an example, 30 means 30 seconds past the chosen minute.


Examples
The following examples illustrate simple tasks.
- Execute every Friday.
FREQ=WEEKLY; BYDAY=FRI;

- Execute every other Friday.
FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI;

- Execute on the last day of every month.
FREQ=MONTHLY; BYMONTHDAY-1;

- Execute on the next to last day of every month.
FREQ=MONTHLY; BYMONTHDAY-2;

- Execute on March 10th.
FREQ=YEARLY; BYMONTH=MAR; BYMONTHDAY=10;

- Execute every 10 days.
FREQ=DAILY; INTERVAL=10;

- Execute daily at 4, 5, and 6PM.
FREQ=DAILY; BYHOUR=16,17,18;

- Execute on the 15th day of every other month.
FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=15;

- Execute on the 29th day of every month.
FREQ=MONTHLY; BYMONTHDAY=29;

- Execute on the second Wednesday of each month.
FREQ=MONTHLY; BYDAY=2WED;

- Execute on the last Friday of the year.
FREQ=YEARLY; BYDAY=-1FRI;

- Execute every 50 hours.
FREQ=HOURLY; INTERVAL=50;

- Execute on the last day of every other month.
FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY-1;

- Execute hourly for the first three days of every month.
FREQ=HOURLY; BYMONTHDAY=1,2,3;

- Execute hourly for the first three days of every month.
FREQ=HOURLY; BYMONTHDAY=1,2,3;

- Execute Monday to Friday at 7:00AM and 3:00PM only.
FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=7,15'

- Execute Last Sunday of every month:
FREQ=MONTHLY; BYDAY=-1SUN

- Execute Every third Friday of the month:
FREQ=MONTHLY; BYDAY=3FRI

- Execute Every second Friday from the end of the month, not from the beginning:
FREQ=MONTHLY; BYDAY=-2FRI

The minus signs before the numbers indicate counting from the end, instead of the beginning.

A repeat interval of "FREQ=MINUTELY;INTERVAL=2;BYHOUR=17; BYMINUTE=2,4,5,50,51,7;" with a start date of 28-FEB-2004 23:00:00 will generate the following schedule:
SUN 29-FEB-2004 17:02:00
SUN 29-FEB-2004 17:04:00
SUN 29-FEB-2004 17:50:00
MON 01-MAR-2004 17:02:00
MON 01-MAR-2004 17:04:00
MON 01-MAR-2004 17:50:00
...

A repeat interval of "FREQ=MONTHLY;BYMONTHDAY=15, -1" with a start date of 29-DEC-2003 9:00:00 will generate the following schedule:
WED 31-DEC-2003 09:00:00
THU 15-JAN-2004 09:00:00
SAT 31-JAN-2004 09:00:00
SUN 15-FEB-2004 09:00:00
SUN 29-FEB-2004 09:00:00
MON 15-MAR-2004 09:00:00
WED 31-MAR-2004 09:00:00
...


-- Create the schedule.
BEGIN
  DBMS_SCHEDULER.create_schedule (
    schedule_name   => 'TEST_HOURLY_SCHEDULE',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0',
    end_date        => NULL,
    comments        => 'Repeats hourly, on the hour, for ever.');
END;
/

-- Display the schedule details.
SELECT schedule_name FROM user_scheduler_schedules;

SCHEDULE_NAME

----------------------
TEST_HOURLY_SCHEDULE

A schedule can be dropped using the DROP_SCHEDULE procedure.
BEGIN
  DBMS_SCHEDULER.drop_schedule (schedule_name => 'TEST_HOURLY_SCHEDULE');
END;
/

What if you wanted to verify if the interval settings are correct? Wouldn't it be nice to see the various dates constructed from the calendar string?
Well, you can get a preview of the calculation of next dates using the EVALUATE_CALENDAR_STRING procedure.
For example running a job every day from Monday through Friday at 7:00AM and 3:00PM, you can check the accuracy of your interval string as follows:

set serveroutput on size 999999

declare
   L_start_date    TIMESTAMP;
   l_next_date     TIMESTAMP;
   l_return_date   TIMESTAMP;
begin
   l_start_date := trunc(SYSTIMESTAMP);
   l_return_date := l_start_date;
   for ctr in 1..10 loop
      dbms_scheduler.evaluate_calendar_string('FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=7,15', l_start_date, l_return_date, l_next_date);
      dbms_output.put_line('Next Run on: ' || to_char(l_next_date,'mm/dd/yyyy hh24:mi:ss'));
      l_return_date := l_next_date;
   end loop;
end;
/

The output is:
Next Run on: 11/13/2013 07:00:00
Next Run on: 11/13/2013 15:00:00
Next Run on: 11/14/2013 07:00:00
Next Run on: 11/14/2013 15:00:00
Next Run on: 11/15/2013 07:00:00
Next Run on: 11/15/2013 15:00:00
Next Run on: 11/18/2013 07:00:00
Next Run on: 11/18/2013 15:00:00
Next Run on: 11/19/2013 07:00:00
Next Run on: 11/19/2013 15:00:00

This confirms that your settings are correct.

Jobs
To actually get The Scheduler to do something, which is kind of the idea, you need to create a job. The job can either run a program that you have created (refer to the previous section) or run its own job, which is defined when the job is defined. The job consists of these principle definitions:
Jobs are created with the dbms_scheduler.create_job package, as shown in this example:

-- Create Diferent jobs.
BEGIN
  -- Job defined entirely by the CREATE JOB procedure.
  DBMS_SCHEDULER.create_job (
    job_name        => 'TEST_FULL_JOB_DEFINITION',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job defined entirely by the CREATE JOB procedure.');

  DBMS_SCHEDULER.create_job (
   job_name=>'CLEAR_DAILY',
   job_type=>'STORED_PROCEDURE',
   job_action=>'JOBS.SP_CLEAR_DAILY',
   start_date=>NULL,
   repeat_interval=>'TRUNC(SYSDATE) + 1/24',
   comments=>'Hourly Clearout Job');

  -- Job defined by an existing program and schedule.
  DBMS_SCHEDULER.create_job (
    job_name      => 'TEST_PROG_SCHED_JOB_DEFINITION',
    program_name  => 'TEST_PLSQL_BLOCK_PROG',
    schedule_name => 'TEST_HOURLY_SCHEDULE',
    enabled       => TRUE,
    comments      => 'Job defined by an existing program and schedule.');

  -- Job defined by existing program and inline schedule.
  DBMS_SCHEDULER.create_job (
    job_name        => 'TEST_PROG_JOB_DEFINITION',
    program_name    => 'TEST_PLSQL_BLOCK_PROG',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job defined by existing program and inline schedule.');

  -- Job defined by existing schedule and inline program.
  DBMS_SCHEDULER.create_job (
     job_name      => 'TEST_SCHED_JOB_DEFINITION',
     schedule_name => 'TEST_HOURLY_SCHEDULE',
     job_type      => 'PLSQL_BLOCK',
     job_action    => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
     enabled       => TRUE,
     comments      => 'Job defined by existing schedule and inline program.');
END;
/

When a Job is created, it is disabled by default (unless you specify enabled => TRUE ).  You need to explicitly enable a Job so it will become active and scheduled.

The repeat_interval attribute defines how often and when the job will repeat. If the repeat_interval is NULL (the default), the job executes only one time and then is removed. When determining the interval, you have two options. First, you can use the older PL/SQL time expressions for defining the program execution intervals.

-- Display job details.
SELECT job_name, enabled FROM user_scheduler_jobs;
JOB_NAME                       ENABL
------------------------------ -----
CLEAR_DAILY                    FALSE
TEST_FULL_JOB_DEFINITION       TRUE


Jobs are normally run asynchronously under the control of the job coordinator, but they can be controlled manually using the RUN_JOB and STOP_JOB procedures.

BEGIN
  -- Run job synchronously.
  DBMS_SCHEDULER.run_job (job_name            => 'TEST_FULL_JOB_DEFINITION',
                          use_current_session => FALSE);

  -- Stop jobs.
  DBMS_SCHEDULER.stop_job (job_name => 'TEST_FULL_JOB_DEFINITION, TEST_PROG_SCHED_JOB_DEFINITION');
END;
/

Jobs can be deleted using the DROP_JOB procedure.
BEGIN
  DBMS_SCHEDULER.drop_job (job_name => 'test_full_job_definition');
  DBMS_SCHEDULER.drop_job (job_name => 'test_prog_sched_job_definition');
  DBMS_SCHEDULER.drop_job (job_name => 'test_prog_job_definition');
  DBMS_SCHEDULER.drop_job (job_name => 'test_sched_job_definition');
END;
/



Job Classes
The new job scheduler also allows you to define job classes, which allow you to define a category of jobs that share common resource usage requirements and other characteristics.
One job can belong to only one job class, though you can change the job class that a given job is assigned to. Any defined job class can belong to a single resource consumer group, and to a single service at any given time. Job classes, then, allow you to assign jobs of different priorities.
For example, administrative jobs (such as backups) might be assigned to an administrative class that is assigned to a resource group that allows for unconstrained activity. Other jobs, with a lesser priority, may be assigned to job classes that are assigned to resource groups that constrain the overall operational overhead of the job, so that those jobs do not inordinately interfere with other, higher-priority jobs. Thus, job classes help you to manage the amount of resources that a given job can consume.
All classes belong to the SYS schema, and to create one requires the manage scheduler privilege.
To create a job class, you use the dbms_scheduler.create_job_class procedure.

exec dbms_scheduler.create_job_class(

job_class_name=>'CLASS_ADMIN',
resource_consumer_group=>'ADMIN_JOBS',
service=>'SERVCE_B');
 
This job class will be called CLASS_ADMIN. It is assigned to a resource consumer group (that we have already created) called ADMIN_JOBS, which will no doubt give administrative jobs pretty unfettered access to resources. This job class is also assigned to a specific service, SERVICE_B, so the administrator can define which service the job class is associated with.
Once the job class is defined, you can define which jobs are members of that class when you create the jobs. Alternatively, you can use the dbms_scheduler.set_ attribute procedure to assign an existing job to that class.
 
-- Display the current resource consumer groups.
SELECT consumer_group FROM dba_rsrc_consumer_groups;

CONSUMER_GROUP
------------------------------
OTHER_GROUPS
DEFAULT_CONSUMER_GROUP
SYS_GROUP
LOW_GROUP
AUTO_TASK_CONSUMER_GROUP

-- Create a job class.
BEGIN
  DBMS_SCHEDULER.create_job_class (
    job_class_name          =>  'TEST_JOB_CLASS',
    resource_consumer_group =>  'LOW_GROUP');
END;
/

-- Display job class details.
SELECT job_class_name, resource_consumer_group FROM dba_scheduler_job_classes;

JOB_CLASS_NAME                 RESOURCE_CONSUMER_GROUP
------------------------------ ------------------------------
DEFAULT_JOB_CLASS
AUTO_TASKS_JOB_CLASS           AUTO_TASK_CONSUMER_GROUP
TEST_JOB_CLASS                 LOW_GROUP


Jobs can be assigned to a job class either during or after creation using the SET_ATTRIBUTE procedure.

BEGIN
  -- Job defined by an existing program and schedule and assigned toa job class.
  DBMS_SCHEDULER.create_job (
    job_name      => 'TEST_PROG_SCHED_CLASS_JOB_DEF',
    program_name  => 'TEST_PLSQL_BLOCK_PROG',
    schedule_name => 'TEST_HOURLY_SCHEDULE',
    job_class     => 'TEST_JOB_CLASS',
    enabled       => TRUE,
    comments      => 'Job defined by an existing program and schedule and assigned toa job class.');

  DBMS_SCHEDULER.set_attribute (
    name      => 'TEST_PROG_SCHED_JOB_DEFINITION',
    attribute => 'JOB_CLASS',
    value     => 'TEST_JOB_CLASS');
END;
/


-- Display job details.
SELECT owner, job_name, job_class, enabled FROM dba_scheduler_jobs;

OWNER                          JOB_NAME                       JOB_CLASS                      ENABL
------------------------------ ------------------------------ ------------------------------ -----
SYS                            PURGE_LOG                      DEFAULT_JOB_CLASS              TRUE
SYS                            GATHER_STATS_JOB               AUTO_TASKS_JOB_CLASS           TRUE
SYS                            TEST_FULL_JOB_DEFINITION       DEFAULT_JOB_CLASS              TRUE
SYS                            TEST_PROG_SCHED_JOB_DEFINITION TEST_JOB_CLASS                 TRUE
SYS                            TEST_PROG_JOB_DEFINITION       DEFAULT_JOB_CLASS              TRUE
SYS                            TEST_SCHED_JOB_DEFINITION      DEFAULT_JOB_CLASS              TRUE
SYS                            TEST_PROG_SCHED_CLASS_JOB_DEF  TEST_JOB_CLASS                 TRUE


Job classes can be dropped using DROP_JOB_CLASS procedure.
BEGIN
  DBMS_SCHEDULER.drop_job_class (
    job_class_name => 'TEST_JOB_CLASS',
    force          => TRUE);
END;
/


Windows
Windows provide the link between the scheduler and the resource manager, allowing different resource plans to be activated at different times. Since job classes point to resource consumer groups, and therefore resource plans, this mechanism allows control over the resources allocated to job classes and their jobs during specific time periods.
Only one window can be active (open) at any time, with one resource plan assigned to the window. The affect of resource plan switches is instantly visible to running jobs which are assigned to job classes. The interaction between the resource manager and the scheduler is beyond the scope of this document.

A window can be created using the CREATE_WINDOW procedure with a predefined or an inline schedule.

BEGIN
  -- Window with a predefined schedule.
  DBMS_SCHEDULER.create_window (
    window_name     => 'TEST_WINDOW_1',
    resource_plan   => NULL,
    schedule_name   => 'TEST_HOURLY_SCHEDULE',
    duration        => INTERVAL '60' MINUTE,
    window_priority => 'LOW',
    comments        => 'Window with a predefined schedule.');

  -- Window with an inline schedule.
  DBMS_SCHEDULER.create_window (
    window_name     => 'TEST_WINDOW_2',
    resource_plan   => NULL,
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0',
    end_date        => NULL,
    duration        => INTERVAL '60' MINUTE,
    window_priority => 'LOW',
    comments        => 'Window with an inline schedule.');
END;
/

-- Display window group details.
SELECT window_name, resource_plan, enabled, active
FROM   dba_scheduler_windows;

WINDOW_NAME                    RESOURCE_PLAN                  ENABL ACTIV
------------------------------ ------------------------------ ----- -----
WEEKNIGHT_WINDOW                                              TRUE  FALSE
WEEKEND_WINDOW                                                TRUE  FALSE
TEST_WINDOW_1                                                 TRUE  FALSE
TEST_WINDOW_2                                                 TRUE  FALSE


Windows can be opened and closed manually using the OPEN_WINDOW and CLOSE_WINDOW procedures.
BEGIN
  -- Open window.
  DBMS_SCHEDULER.open_window (
   window_name => 'TEST_WINDOW_2',
   duration    => INTERVAL '1' MINUTE,
   force       => TRUE);
END;
/

-- Display window group details.
SELECT window_name, resource_plan, enabled, active
FROM   dba_scheduler_windows;

WINDOW_NAME                    RESOURCE_PLAN                  ENABL ACTIV
------------------------------ ------------------------------ ----- -----
WEEKNIGHT_WINDOW                                              TRUE  FALSE
WEEKEND_WINDOW                                                TRUE  FALSE
TEST_WINDOW_1                                                 TRUE  FALSE
TEST_WINDOW_2                                                 TRUE  TRUE


BEGIN
  -- Close window.
  DBMS_SCHEDULER.close_window ( window_name => 'TEST_WINDOW_2');
END;
/


-- Display window group details.
SELECT window_name, resource_plan, enabled, active
FROM   dba_scheduler_windows;

WINDOW_NAME                    RESOURCE_PLAN                  ENABL ACTIV
------------------------------ ------------------------------ ----- -----
WEEKNIGHT_WINDOW                                              TRUE  FALSE
WEEKEND_WINDOW                                                TRUE  FALSE
TEST_WINDOW_1                                                 TRUE  FALSE
TEST_WINDOW_2                                                 TRUE  FALSE


Windows can be dropped using the DROP_WINDOW procedure.

BEGIN
  DBMS_SCHEDULER.drop_window (
    window_name => 'TEST_WINDOW_1',
    force       => TRUE);

  DBMS_SCHEDULER.drop_window (
    window_name => 'TEST_WINDOW_2',
    force       => TRUE);
END;
/


Windows Groups
A window group is a collection of related windows. It can be created with 0, 1 or many windows as group members using the CREATE_WINDOW_GROUP procedure.
BEGIN
  DBMS_SCHEDULER.create_window_group (
    group_name  => 'TEST_WINDOW_GROUP',
    window_list => 'TEST_WINDOW_1, TEST_WINDOW_2',
    comments    => 'A test window group');
END;
/

-- Display window group details.
SELECT window_group_name, enabled, number_of_windowS
FROM   dba_scheduler_window_groups;

WINDOW_GROUP_NAME              ENABL NUMBER_OF_WINDOWS
------------------------------ ----- -----------------
MAINTENANCE_WINDOW_GROUP       TRUE                  2
TEST_WINDOW_GROUP              TRUE                  2


Windows can be added and removed from a group using the ADD_WINDOW_GROUP_MEMBER and REMOVE_WINDOW_GROUP_MEMBER procedures.
BEGIN
  -- Create a new window.
  DBMS_SCHEDULER.create_window (
    window_name     => 'TEST_WINDOW_3',
    resource_plan   => NULL,
    schedule_name   => 'test_hourly_schedule',
    duration        => INTERVAL '60' MINUTE,
    window_priority => 'LOW',
    comments        => 'Window with a predefined schedule.');

  DBMS_SCHEDULER.add_window_group_member (
    group_name  => 'TEST_WINDOW_GROUP',
    window_list => 'TEST_WINDOW_3');
END;
/

-- Display window group members.
SELECT window_group_name, window_name
FROM   dba_scheduler_wingroup_members;

WINDOW_GROUP_NAME              WINDOW_NAME
------------------------------ ------------------------------
MAINTENANCE_WINDOW_GROUP       WEEKNIGHT_WINDOW
MAINTENANCE_WINDOW_GROUP       WEEKEND_WINDOW
TEST_WINDOW_GROUP              TEST_WINDOW_1
TEST_WINDOW_GROUP              TEST_WINDOW_2
TEST_WINDOW_GROUP              TEST_WINDOW_3

BEGIN
  DBMS_SCHEDULER.remove_window_group_member (
    group_name  => 'TEST_WINDOW_GROUP',
    window_list => 'TEST_WINDOW_3');
END;
/

Window groups can be dropped using the DROP_WINDOW_GROUP procedure.
BEGIN
  DBMS_SCHEDULER.drop_window_group (
    group_name => 'test_window_group',
    force      => TRUE);
END;
/


Enable, Disable and Attributes
All applicable scheduler objects can be enabled and disabled using the overloaded ENABLE and DISABLE procedures.

BEGIN
  -- Enable programs and jobs.
  DBMS_SCHEDULER.enable (name => 'TEST_STORED_PROCEDURE_PROG');
  DBMS_SCHEDULER.enable (name => 'TEST_FULL_JOB_DEFINITION');

  -- Disable programs and jobs.
  DBMS_SCHEDULER.disable (name => 'TEST_STORED_PROCEDURE_PROG');
  DBMS_SCHEDULER.disable (name => 'TEST_FULL_JOB_DEFINITION');
END;
/
The values for individual attributes of all scheduler objects can be altered using one of the SET_ATTRIBUTE overloads.

BEGIN
  DBMS_SCHEDULER.set_attribute (
    name      => 'HOURLY_SCHEDULE',
    attribute => 'repeat_interval',
    value     => 'freq=hourly; byminute=30');
END;
/
The values can be set to NULL using the SET_ATTRIBUTE_NULL procedure.

BEGIN
  DBMS_SCHEDULER.set_attribute_null (
    name      => 'HOURLY_SCHEDULE',
    attribute => 'repeat_interval');
END;
/





Example: Converting DB jobs to Scheduler
Let's say we have a job in DBA_JOBS that we want to convert to the Scheduler.
select job, log_user, next_date, next_sec, interval, what
from dba_jobs
where job=152152;

JOB LOG_USER NEXT_DATE NEXT_SEC INTERVAL WHAT
---------- ---------- ---------- -------- ------------------------- --------------------
152152 SCOTT 05/01/2006 01:00:00 trunc(sysdate+1) + 1/24 SCOTTS_PROC;

This job belongs to SCOTT, and executes SCOTTS_PROC every day at 1 am. Let's look at how to create and run a similar job in the Scheduler. The rough equivalents to DBMS_JOB.SUBMIT and DBMS_JOB.RUN are:
DBMS_JOB DBMS_SCHEDULER
dbms_job.run dbms_scheduler.run_job
dbms_job.submit dbms_scheduler.create_job

DBMS_SCHEDULER.CREATE_JOB is an overloaded procedure; in this example, we'll look at just one of many ways to call it. Here's the call:

begin
dbms_scheduler.create_job
(job_name => 'SCOTT.RUN_SCOTTS_PROC',
job_type => 'STORED_PROCEDURE',
job_action=> 'SCOTT.SCOTTS_PROC',
start_date=> trunc(sysdate+1)+1/24,
repeat_interval=> 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT; BYHOUR=1;',
enabled=>true,
auto_drop=>false,
comments=>'Converted from job 152152');
end;

There are a lot of things to notice here; let's go through the call line by line.

  1. Instead of a number, the job's identified by the name you pass as job_name. Job names follow the standard Oracle naming conventions. SYS can create a job for anyone. So, job_name => 'SCOTT.RUN_SCOTTS_PROC' creates a job in SCOTT's schema named RUN_SCOTTS_PROC .
  2. You need to specify the job type. As in the old job scheduling mechanism, you can schedule anonymous blocks (job_type: 'PLSQL_BLOCK') or stored procedures (job_type: 'STORED_PROCEDURE'). You can also schedule programs that run outside the database (job_type: 'EXECUTABLE') or schedule "job chains" (job_type: 'CHAIN'). A job chain is a sequence of jobs.
  3. job_action is the equivalent of "what" in dbms_job.submit. Note that if you're scheduling a stored procedure, you don't put a semicolon after the procedure name, as you did in dbms_job.submit . Note also that if you want to pass arguments to a stored procedure, like UPDATE_INDEXES('SCOTT'), you have to either wrap the stored procedure in an anonymous pl/sql block, or set the arguments with a separate call to SET_JOB_ARGUMENT_VALUE - a topic we won't cover here.
  4. start_date is the first date the job will execute. Note that if you specify the repeat_interval using calendaring syntax, which we'll cover shortly, start-date is used as a reference only - the job won't actually execute until the next date that matches your repeat_interval. For example, if you set the repeat interval to every Thursday, and pass a start date that's on a Tuesday, then the job will execute on the first Thursday following the start date.
  5. This call passes repeat_interval in calendaring syntax, which we'll discuss below. You could also pass it just as you had it in DBA_JOBS, as 'trunc(sysdate+1) + 1/24'.
  6. In DBMS_SCHEDULER, unlike DBMS_JOB, the default is that new jobs are disabled. If you want your job to be enabled as soon as you create it, as it would have been with DBMS_JOB, then passenabled=>true.
  7. You can control whether or not a one-time job is dropped after it's run by setting auto_drop to FALSE.
  8. You can pass in comments, which are displayed alongside the job in the static data dictionary view that shows Scheduler jobs.

After you run this call to create_job, there's one more thing to notice: you don't have to COMMIT. The results are immediately visible to all users in the DBA_SCHEDULER_JOBS table:

OWNER JOB_NAME        JOB_ACTION        START_DATE REPEAT_INTERVAL STATE     COMMENTS
----- --------------- ----------------- ---------- --------------- --------- -------------------------
SCOTT RUN_SCOTTS_PROC SCOTT.SCOTTS_PROC 05/08/2006 FREQ=DAILY; BYD SCHEDULED Converted from job 152152

What if you want to run this job right away? Use dbms_scheduler.run_job:

connect scott/tiger
begin
DBMS_SCHEDULER.RUN_JOB (
job_name => 'RUN_SCOTTS_PROC',
use_current_session => false);
end;
/

Note that the default for use_current_session is TRUE -- that is, if you don't pass this parameter, RUN_SCOTTS_PROC will run synchronously, ie. in your current session, instead of runningasynchronously, ie. being picked up and run by a job slave.

Job Run Log
While a job is running, it won't show up in DBA_JOBS_RUNNING; instead, it's in DBA_SCHEDULER_RUNNING_JOBS. Once the job has executed, log rows are written to DBA_SCHEDULER_JOB_LOG and DBA_SCHEDULER_JOB_RUN_DETAILS. These tables record the exit status (success, failure) of each job run, the run dates and duration, and provide any additional details such as error messages, who stopped a stopped job, etc.

More Job Tools
If you're going to move jobs to the Scheduler, you'll need to know how to change their next run dates (or other attributes), how to disable and reenable them, and how to kill a running job. Fortunately, these are all easy tasks in DBMS_SCHEDULER. Here are the rough equivalents to the DBMS_JOB commands:
DBMS_JOB Purpose DBMS_SCHEDULER
dbms_job.next_date Change the next time a job will run dbms_scheduler.set_atrribute - change the repeat_interval or start_date
dbms_job.what Change the program a job runs dbms_scheduler.set_atrribute - change job_action
dbms_job.interval Change how often a job runs dbms_scheduler.set_atrribute - change repeat_interval
dbms_job.change Change job attributes dbms_scheduler.set_atrribute - change the appropriate attribute
dbms_job.broken Mark a job as BROKEN, or unmark it dbms_scheduler.disable / dbms_scheduler.enable
alter system kill session... Stop a running job dbms_scheduler.stop_job


Special Notes
- To review the execution times of a job:
SELECT JOB_NAME, STATUS, ERROR# FROM user_SCHEDULER_JOB_RUN_DETAILS;

- To review the errors of a Job:
select status, error#, substr(additional_info,1,500) 
  from user_scheduler_job_run_details ;

- By default, XE on Windows sets up the scheduler service to disabled. Enable and start it

- If you are trying to setup a Windows .bat job, you may need to perform the following:
   job_action=>'c:\windows\system32\cmd.exe /c C:\AA\Gen_Del_Images.bat > nul',



Full Example to perform Backups
Calls a Windows bat file from inside Oracle, using the dbms_scheduler syntax:
begin
  dbms_scheduler.drop_job (job_name=>'daily_backup');
end;
/

begin 
  dbms_scheduler.drop_program(program_name   => 'backup_database');
end;
/

begin 
  dbms_scheduler.drop_schedule(schedule_name => 'daily_at_4am_except_monday');
end;
/

begin
  dbms_scheduler.create_schedule(
       schedule_name   => 'daily_at_4am_except_monday',
       repeat_interval => 'FREQ=DAILY; INTERVAL=1; BYDAY=TUE,WED,THU,FRI,SAT,SUN; BYHOUR=4',
       comments        => 'schedule to run daily at 4am except on mondays');
  dbms_scheduler.create_program
    (  program_name   => 'backup_database',
       program_type   => 'EXECUTABLE',
       program_action => 'd:\oracle\product\admin\dw\scripts\backup_dw.bat > nul',
       enabled        => TRUE,
       comments       => 'Backup dw database using rman and then backup rman database via hot backup.'
    );
  dbms_scheduler.create_job (
        job_name=>'daily_backup',
        program_name =>'backup_database',
        schedule_name=> 'DAILY_AT_4AM_EXCEPT_MONDAY',
        enabled      => true,
        comments     => 'backs up the dw and rman databases daily at 4am except on for mondays.'
  );
end;
/

select * from dba_scheduler_jobs;



Recreate (or Copy) Oracle Scheduler Jobs
In this example, we will use some scripts that you can used to re-create scripts and run them on another environment.

Step 1: Run below 2 queries those generate a script output.  Save the output
Query 1: Below query generates ‘create schedule’ script that can be run in other environment. This query should be first because we want to have schedules created before jobs will be created.
Note:
If you have single quotes within comments column, replace them with double single quotes
select 'BEGIN DBMS_SCHEDULER.create_schedule ('|| CHR(10)
       ||q'!schedule_name   => '!'||schedule_name||q'!',!'|| CHR(10)
       ||'start_date   => SYSTIMESTAMP,'|| CHR(10)
       ||'repeat_interval   => '''||repeat_interval||''','|| CHR(10)
       ||'end_date   => '||decode(end_date,null,'NULL')||','|| CHR(10)
       ||'comments   => '||''''||comments||''''||chr(10)
       ||');'||CHR(10)
       ||'END;' scheduler_script
from dba_scheduler_schedules
WHERE OWNER = upper('&OWNER');


Query 2: Below query generates a script for ‘creating Jobs’
Note: If you have single quotes within comments column, replace them with double single quotes
select
       case when schedule_name is not null then
           'BEGIN DBMS_SCHEDULER.create_job ('|| CHR(10)
           ||'job_name => '''||JOB_NAME||''','||CHR(10)
           ||'job_type => '''||JOB_TYPE||''','||CHR(10)
           ||'job_action => '''||job_action||''','||CHR(10)
           ||'schedule_name => '''||schedule_name||''','||CHR(10)
           ||'enabled => '||enabled||','||CHR(10)
           ||'comments   => '||''''||comments||''''||chr(10)
           ||');'||CHR(10)
           ||'END;'
       else
           'BEGIN DBMS_SCHEDULER.create_job ('|| CHR(10)
           ||'job_name => '''||JOB_NAME||''','||CHR(10)
           ||'job_type => '''||JOB_TYPE||''','||CHR(10)
           ||'job_action => '''||job_action||''','||CHR(10)
           ||'start_date   => SYSTIMESTAMP,'|| CHR(10)
           ||'repeat_interval   => '''||repeat_interval||''','|| CHR(10)  
           ||'enabled => '||enabled||','||CHR(10)
           ||'comments   => '||''''||comments||''''||chr(10)
           ||');'||CHR(10)
           ||'END;'
       end job_script      
from  dba_scheduler_jobs j
where j.owner =upper('&OWNER');


Note: if you use  Class, Windows , and Window groups, it is easy to regenerate them using above concept.

Below Sample output generated by above queries and formatted
BEGIN
   DBMS_SCHEDULER.create_schedule (
      schedule_name     => WKND_MAINTENANCE',
      start_date        => SYSTIMESTAMP,
      repeat_interval   => 'freq=WEEKLY; interval=1; byday=SUN; byhour=10 ;byminute=0;bysecond=0',
      end_date          => NULL,
      comments          => 'This scheduler runs every Sunday 10 AM. Any backend jobs, which do not require webserver down, can be assigned to this schedule');
END;

BEGIN
   DBMS_SCHEDULER.create_job (
      job_name        => 'TABLESPACE_USAGE',
      job_type        => 'STORED_PROCEDURE',
      job_action      => 'PG_UTILITY.SP_TABLESAPCESTATUS_EMAIL',
      schedule_name   => WKND_MAINTENANCE',
      enabled         => TRUE,
      comments        => 'This job runs a pkg proc to send email report on Tablespace usage.. Fits into GAIMS weekend schedule - every sunday 10 AM');
END;
BEGIN
   DBMS_SCHEDULER.create_job (
      job_name          => 'INVENTORYCALC_JOB',
      job_type          => 'STORED_PROCEDURE',
      job_action        => 'sp_pg_inventorycalc',
      start_date        => SYSTIMESTAMP,
      repeat_interval   => 'FREQ=MINUTELY; INTERVAL=15',
      enabled           => TRUE,
      comments          => 'Re-calculates Inventory for records changed in SPWI.');
END;


Step 2: Connect to Target database where you want to re-create these schedules and jobs, Then execute the output generated in Step 1
Advantages:
•    Saves lot of time.
•    Not to worry about being incorrect job name and incorrect schedules being created.



More resources for 10gr2 and 11g
Scheduler (DBMS_SCHEDULER) Enhancements in Oracle 10g Database Release 2
http://www.oracle-base.com/articles/10g/SchedulerEnhancements_10gR2.php

Scheduler (DBMS_SCHEDULER) Enhancements in Oracle Database 11g Release 1
http://www.oracle-base.com/articles/11g/SchedulerEnhancements_11gR1.php

Scheduler (DBMS_SCHEDULER) Enhancements in Oracle Database 11g Release 2
http://www.oracle-base.com/articles/11g/SchedulerEnhancements_11gR2.php

Remote Scheduler Agent Installation for Oracle 11g Release 2
http://www.oracle-base.com/articles/11g/SchedulerAgentInstallation_11gR2.php