Using Window Groups

Window groups provide an easy way to schedule jobs that must run during multiple time periods throughout the day, week, and so on. If you create a window group, add windows to it, and then name this window group in a job's schedule_name attribute, the job runs during all the windows in the window group.

Window groups reside in the SYS schema. This section introduces you to basic window group tasks, and discusses the following topics:

Window Group Tasks and Their Procedures
Creating Window Groups
Dropping Window Groups
Adding a Member to a Window Group
Dropping a Member from a Window Group
Enabling a Window Group
Disabling a Window Group

Window Group Tasks and Their Procedures

Table 27-6 illustrates common window group tasks and the procedures you use to handle them.

Table 27-6 Window Group Tasks and Their Procedures

Task	Procedure	Privilege Needed
Create a window group	`CREATE_WINDOW_GROUP`	`MANAGE` `SCHEDULER`
Drop a window group	`DROP_WINDOW_GROUP`	`MANAGE` `SCHEDULER`
Add a member to a window group	`ADD_WINDOW_GROUP_MEMBER`	`MANAGE` `SCHEDULER`
Drop a member to a window group	`REMOVE_WINDOW_GROUP_MEMBER`	`MANAGE` `SCHEDULER`
Enable a window group	`ENABLE`	`MANAGE` `SCHEDULER`
Disable a window group	`DISABLE`	`MANAGE` `SCHEDULER`

See "Scheduler Privileges" for further information regarding privileges.

Creating Wi ndow Groups

You create a window group by using the CREATE_WINDOW_GROUP procedure or Enterprise Manager. You can specify the member windows of the group when you create the group, or you can add them later using the ADD_WINDOW_GROUP_MEMBER procedure. A window group cannot be a member of another window group. You can, however, create a window group that has no members.

If you create a window group and you specify a member window that does not exist, an error is generated and the window group is not created. If a window is already a member of a window group, it is not added again.

Window groups are created in the SYS schema. Window groups, like windows, are created with access to PUBLIC, therefore, no privileges are required to access window groups.

The following statement creates a window group called downtime and adds two windows (weeknights and weekends) to it:

BEGIN
DBMS_SCHEDULER.CREATE_WINDOW_GROUP (
   group_name   =>  'downtime',
   window_list  =>  'weeknights, weekends');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the CREATE_WINDOW_GROUP procedure.

Dropping Windo w Groups

You drop one or more window groups by using the DROP_WINDOW_GROUP procedure or Enterprise Manager. This call will drop the window group but not the windows that are members of this window group. If you want to drop all the windows that are members of this group but not the window group itself, you can use the DROP_WINDOW procedure and provide the name of the window group to the call.

You can drop several window groups in one call by providing a comma-delimited list of window group names to the DROP_WINDOW_GROUP procedure call. For example, the following statement drops three window groups:

BEGIN
DBMS_SCHEDULER.DROP_WINDOW_GROUP('windowgroup1, windowgroup2, windowgroup3');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for detailed information about adding and dropping window groups.

Adding a Member to a Window Group

You add windows to a window group by using the ADD_WINDOW_GROUP_MEMBER procedure.

You can add several members to a window group in one call, by specifying a comma-delimited list of windows. For example, the following statement adds three windows to the window group window_group1:

BEGIN
DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER ('window_group1',
   'window1, window2, window3');
END;
/

If an already open window is added to a window group, the Scheduler will not start jobs that point to this window group until the next window in the window group opens.

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the ADD_WINDOW_GROUP_MEMBER procedure.

Dropping a Member from a Window Group

You can drop one or more windows from a window group by using the REMOVE_WINDOW_GROUP_MEMBER procedure or Enterprise Manager. Jobs with the stop_on_window_close flag set will only be stopped when a window closes. Dropping an open window from a window group has no impact on this.

You can remove several members from a window group in one call by specifying a comma-delimited list of windows. For example, the following statement drops three windows:

BEGIN
DBMS_SCHEDULER.REMOVE_WINDOW_GROUP_MEMBER('window_group1', 'window1, window2,
   window3');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the REMOVE_WINDOW_GROUP_MEMBER procedure.

Enabling a Wi ndow Group

You enable one or more window groups using the ENABLE procedure or Enterprise Manager. By default, window groups are created ENABLED. For example:

BEGIN
DBMS_SCHEDULER.ENABLE('sys.windowgroup1', 'sys.windowgroup2, sys.windowgroup3');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the ENABLE procedure.

Disabling a Window Group

You disable a window group using the DISABLE procedure or Enterprise Manager. This means that jobs with the window group as a schedule will not run even if the member windows open, however, the metadata of the window group is still there, so it can be reenabled. Note that the members of the window group will still open.

You can also disable several window groups in one call by providing a comma-delimited list of window group names to the DISABLE procedure call. For example, the following statement disables three window groups:

BEGIN
DBMS_SCHEDULER.DISABLE('sys.windowgroup1, sys.windowgroup2, sys.windowgroup3');
END;
/

Note that, in this example, the window group will be disabled, but the windows that are members of the window group will not be disabled.

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the DISABLE procedure.