Oracle DBA_SCHEDULER_WINDOWS

Oracle Database Tips by Donald Burleson

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

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

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 11.24 shows the Scheduler Windows screen in the OEM DB Control.

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.

  window_name             IN VARCHAR2,

  duration                IN INTERVAL DAY TO SECOND,

  force                   IN BOOLEAN DEFAULT FALSE)

  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, and then closes, test_window_2.  Notice how the active window switches back to weekend_window when test_window_2 is closed.

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.

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.

  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:

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

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.