Oracle PL/SQL for DBAs

Creating a Window

You can create a window using the CREATE_WINDOW procedure in the DBMS_SCHEDULER package. Suppose that I want to create a window called LOW_LOAD, which has the following characteristics:

• It starts each day at 3:00 A.M.

• It is 14 hours long.

• It uses the resource plan OLTP_PLAN.

I can create this window as follows.

      1  BEGIN
      2     DBMS_SCHEDULER.create_window (window_name        => 'low_load',
      3            resource_plan      => 'OLTP_PLAN',
      4            schedule_name      => 'SCHED_MANAGER.EVERY_DAY',
      5            DURATION           => NUMTODSINTERVAL
      6                                        (840,
      7                                         'minute'
      8                                         ),
      9            comments           => 'This is a low load window'
     10            );
     11 END;

Let’s examine the lines in detail in the following table.

Line	Description
2	Name of the window.
3	Name of the resource manager plan, OLTP_PLAN, which was previously created.
4	Name of the schedule, which was previously created. It starts at 3:00 A.M. every day.
5–8	Duration of this window. The duration parameter is specified in the datatype INTERVAL DAY TO SECOND. I must specify the 14-hour period in the manner shown.
9	Comment for the window.

Once this window has been created, I can create a job that uses this window. When I run the CREATE_JOB procedure, I can specify the window name in the schedule_name parameter, as shown below. Earlier, I described how to set this parameter to define a schedule. Here, the same parameter I used to specify a window named LOW_LOAD.

 BEGIN DBMS_SCHEDULER.create_job (job_name => 'Calculate_Daily_Interest', program_name => 'INTEREST_ADMIN.CALC_INTEREST', schedule_name ...