Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle Scheduling Windows

Oracle Tips by Burleson Consulting

Windows

Windows define the times when resource plans are active.  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 associated jobs during specific time periods.  A window can be assigned to the schedule_name parameter of a job instead of a schedule object.

Only one window can be active at any time, with one resource plan assigned to the window.  The effects of resource plan switches are instantly visible to running jobs that are assigned to job classes.

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

PROCEDURE create_window (
  window_name             IN VARCHAR2,
  resource_plan           IN VARCHAR2,
  schedule_name           IN VARCHAR2,
  duration                IN INTERVAL DAY TO SECOND,
  window_priority         IN VARCHAR2                 DEFAULT 'LOW',
  comments                IN VARCHAR2                 DEFAULT NULL)

PROCEDURE create_window (
  window_name             IN VARCHAR2,
  resource_plan           IN VARCHAR2,
  start_date              IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
  repeat_interval         IN VARCHAR2,
  end_date                IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
  duration                IN INTERVAL DAY TO SECOND,
  window_priority         IN VARCHAR2                 DEFAULT 'LOW',
  comments                IN VARCHAR2                 DEFAULT NULL)

The parameters associated with these procedures and their usage are as follows:

* window_name - A name that uniquely identifies the window.

* resource_plan - The resource plan associated with the window.  When the window opens, the system switches to use the associated resource plan.  When the window closes, the system switches back to the previous resource plan.

* schedule_name - The name of the schedule associated with the window.  If this is specified, the start_date, repeat_interval and end_date must be NULL.

* start_date - The date when this window will take effect.  This may be in the future if the window is to be setup in advance.

* repeat_interval - The definition of how often the window should open.  A value of NULL indicates that the window should only open once.

* end_date - The date when this window will stop.  This combined with the start_date parameter enables a window to be scheduled for a finite period of time.

* duration - The length of time in minutes the window should remain open.

* window_priority - The priority (LOW or HIGH) of the window.  In the event of multiple windows opening at the same time, windows with a high priority take precedence over windows with a low priority, which is the default.

* comments - Free text allowing the user to record additional information.

The following code shows how the create_window procedures can be used:

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 '30' MINUTE,
    window_priority => 'LOW',
    comments        => 'Window with a predefined schedule.');
END;
/

BEGIN
  -- 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 '30' MINUTE,
    window_priority => 'LOW',
    comments        => 'Window with an inline schedule.');
END;
/

The SYS user is the owner of all windows, so any schedules referenced by them must also be owned by SYS.

Figure 2.13 shows the Create Window screen in the OEM 10g DB Control.

Figure 2.13 ? OEM 10g DB Control: Create Window

Information about windows can be displayed using the dba_scheduler_windows view. The following script uses this view:

* windows.sql

select
   window_name,
   resource_plan,
   enabled,
   active
from
   dba_scheduler_windows
;

The output from the windows.sql script is displayed below.

job_user@db10g> @windows

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

4 rows selected.

Figure 2.14 shows the Scheduler Windows screen in the OEM 10g DB Control.

Figure 2.14 ? OEM 10g DB Control: Scheduler Windows

The server normally controls the opening and closing of windows, but they also can be opened and closed manually using the open_window and close_window procedures.

PROCEDURE open_window (
  window_name             IN VARCHAR2,
  duration                IN INTERVAL DAY TO SECOND,
  force                   IN BOOLEAN DEFAULT FALSE)
PROCEDURE close_window (
  window_name             IN VARCHAR2)

The parameters associated with these procedures and their usage are as follows:

* window_name - A name that uniquely identifies the window.

* duration - The length of time, in minutes, the window should remain open.

* force - When set to FALSE, attempting to open a window when one is already open will result in an error unless the currently open window is the one that is attempting to open.  In this case, the close time is set to the current system time plus the specified duration.

Closing a window causes all jobs associated with that window to be stopped.

The following example opens then closes test_window_2.  Notice how the active window switches back to weekend_window when test_window_2 is closed.

BEGIN
  -- Open window.
  DBMS_SCHEDULER.open_window (
   window_name => 'test_window_2',
   duration    => INTERVAL '1' MINUTE,
   force       => TRUE);
END;
/
SQL> @windows

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

4 rows selected.

BEGIN
  -- Close window.

 
DBMS_SCHEDULER.close_window (
   window_name => 'test_window_2');
END;
/

SQL> @windows

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


4 rows selected.

Windows can be removed using the drop_window procedure.

PROCEDURE drop_window (
  window_name             IN VARCHAR2,
  force                   IN BOOLEAN DEFAULT FALSE)

The parameters associated with this procedure and their usage are as follows:

* window_name - A single or comma separated list of window names.  If a window group name is specified, all windows within that group will be dropped.  In addition, all jobs that use the specified window or window group as a schedule will be disabled, although running jobs will complete normally.

* force - When set to FALSE, attempting to drop an open window will result in an error.  When set to TRUE the open window is closed before it is dropped.

The following example drops the two test windows that were created earlier:

BEGIN
  DBMS_SCHEDULER.drop_window (
    window_name => 'test_window_1,test_window_2',
    force       => TRUE);
END;
/

The output from the windows.sql script confirms that the windows have been removed successfully.

SQL> @windows

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

2 rows selected.

The following section will show how to group related windows together using window groups.

 

This is an excerpt from the book "Oracle Job Scheduling" by Dr. Tim Hall.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle job scheduling scripts.


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational