Oracle DBA Lesson 92 Part 1 — Change a maintenance window with a backout

Choose maintenance time from measured workload demand, edit a Scheduler window, preserve the original configuration, and check the next actual run before keeping the change. A backout restores the captured settings, including an originally disabled state.

Oracle DBA Lesson 92 Part 1 — Change a maintenance window with a backout

Start with demand and a measurable decision

An overlap of 15 minutes between maintenance and a batch identifies a candidate for investigation. Resource use and waits are needed to establish the cause of a slowdown. Change the schedule when that investigation justifies a change.

When the quiet period is 02:00–03:00 and recent maintenance runs needed 35 minutes, a 45-minute window provides 10 minutes of headroom and fits inside that hour:

35 minutes of recent work + 10 minutes of planned headroom = 45 minutes

These quantities justify a proposal. The next run must establish whether maintenance completes with the resources, data volume, container availability, and task eligibility at the new time. Do not shorten a window merely to remove an overlap if that prevents needed maintenance from completing.

Before changing anything, write acceptance criteria for a representative cycle:

• Actual opening matches the intended calendar and time zone.
• Required maintenance completes within the available opportunity, with acceptable object or task coverage.
• Batch elapsed time and resource/wait behavior meet the agreed workload target.
• Client eligibility, other jobs using the window, and resource-plan behavior match the intended scope.

A successful job status describes a run. Inspect the required work to establish coverage.

Check the current container

Record the release update, edition, database, and current container. Maintenance settings are scoped to the current container. A change in the root and a change in an application container are separate settings. In SQL*Plus:

SHOW USER
SHOW CON_NAME

SELECT SYS_CONTEXT('USERENV','DB_NAME') AS db_name,
       SYS_CONTEXT('USERENV','CON_NAME') AS container_name,
       SYS_CONTEXT('USERENV','SESSION_USER') AS session_user,
       SESSIONTIMEZONE AS session_time_zone,
       SYSTIMESTAMP AS observed_at
FROM dual;

Keep each timestamp with its time zone. See Maintenance in a multitenant database.

Create, disable and inspect a custom window

The commands use the window name MAINT_TEST. Windows are Scheduler objects in the SYS schema, so DISABLE, ENABLE, and SET_ATTRIBUTE identify it as SYS.MAINT_TEST. The schema qualifier does not mean the session connects as SYS. Creating or managing a window still requires the Scheduler privilege that covers that operation.

Keep this window outside MAINTENANCE_WINDOW_GROUP, attach no jobs to it, and keep its first opening in the future until it is disabled. New Scheduler windows are created enabled. CREATE_WINDOW has no enabled => FALSE parameter. The future start leaves time for the immediately following disable call. If creation succeeds and a later statement fails, inspect and repair the actual state before continuing. A PL/SQL block is not an automatic transaction rollback for these administrative changes.

First check for a name collision:

SELECT window_name, enabled, active, next_start_date
FROM dba_scheduler_windows
WHERE window_name = 'MAINT_TEST';

Create the window only when that name is absent. Use a future reference date and a daily 02:00 calendar:

BEGIN
  DBMS_SCHEDULER.CREATE_WINDOW(
    window_name     => 'MAINT_TEST',
    resource_plan   => NULL,
    start_date      => SYSTIMESTAMP + INTERVAL '1' DAY,
    repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
    duration        => NUMTODSINTERVAL(30,'MINUTE'),
    comments        => 'Disposable window edit practice');

  DBMS_SCHEDULER.DISABLE('SYS.MAINT_TEST');
END;
/

START_DATE is a reference timestamp. The calendaring expression determines the first matching opening at or after it. SYSTIMESTAMP + INTERVAL '1' DAY is at least a day ahead when evaluated. The window is created enabled and then explicitly disabled. Confirm ENABLED = FALSE and ACTIVE = FALSE immediately afterward. Do not assume the whole block succeeded merely because the object exists.

RESOURCE_PLAN => NULL leaves the plan already in effect when the window opens. It does not select DEFAULT_MAINTENANCE_PLAN or define a resource allocation. The window remains closed and disabled.

For this duration change, the calendar inherits the time zone from the supplied reference timestamp. A real schedule must preserve its intended region or offset explicitly. Region names support daylight-saving transitions. A fixed offset has different behavior. When a repeating window has no START_DATE, inspect the Scheduler's default_timezone attribute. A session display time zone alone does not define a stored calendar's time zone.

Capture the initial configuration and memberships:

SELECT window_name, enabled, active, start_date, end_date,
       schedule_owner, schedule_name, repeat_interval,
       duration, resource_plan, window_priority, next_start_date
FROM dba_scheduler_windows
WHERE window_name = 'MAINT_TEST';

SELECT window_group_name, window_name
FROM dba_scheduler_wingroup_members
WHERE window_name = 'MAINT_TEST';

The expected initial duration is 30 minutes. Confirm the calendar and the membership results. If MAINTENANCE_WINDOW_GROUP appears, stop. The duration edit below is for a window outside that group.

See Scheduling jobs, windows, enablement and time zones, DBMS_SCHEDULER, Scheduler administration and privileges, and Window-group members.

Change the duration and interpret the stored result

With the window confirmed closed and disabled:

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => 'SYS.MAINT_TEST',
    attribute => 'DURATION',
    value     => NUMTODSINTERVAL(45,'MINUTE'));
END;
/

SELECT window_name, enabled, duration, repeat_interval
FROM dba_scheduler_windows
WHERE window_name = 'MAINT_TEST';

NUMTODSINTERVAL(45,'MINUTE') supplies an interval value of 45 minutes. DURATION controls how long the window can remain open. The daily 02:00 REPEAT_INTERVAL remains unchanged. A disabled window stays disabled when altered.

Verify these properties against the captured initial state:

Property	Required state after the duration edit
ENABLED	FALSE
ACTIVE	FALSE
DURATION	45 minutes, commonly displayed as +000 00:45:00
REPEAT_INTERVAL	Exactly the same daily 02:00 expression
Other saved attributes	Unchanged
Maintenance membership	Absent

The stored duration establishes the configured opportunity. Actual run and completion evidence requires a scheduled operational cycle. This disabled window does not perform that cycle.

See Window catalog columns.

Membership and client configuration

A maintenance window is a Scheduler window belonging to MAINTENANCE_WINDOW_GROUP. Creating an arbitrary Scheduler window does not add that membership. Automatic maintenance also depends on the framework and client settings. The duration edit does not change group membership or client settings.

For an existing maintenance window, inspect its per-window client settings:

SELECT window_name, autotask_status, optimizer_stats,
       segment_advisor, sql_tune_advisor
FROM dba_autotask_window_clients
ORDER BY window_name;

Keep the relevant row with the configuration capture. DBMS_AUTO_TASK_ADMIN.ENABLE and DISABLE can target a particular client and named window. A null window_name can instead affect all windows for that client, so a client change must specify and verify its intended scope and exact reversal. Do not use a blanket client disable to solve one overlap. If statistics collection is disabled in one opportunity, the requirement to maintain adequate optimizer statistics remains and needs an alternative plan.

See Per-window client settings and DBMS_AUTO_TASK_ADMIN.

Apply the method to an existing window

1. Capture the database, the container, and the original window definition: START_DATE with its time zone, END_DATE, the inline REPEAT_INTERVAL or the named schedule definition, DURATION, the resource plan, the priority, every group membership, and ENABLED. Capture the per-window client settings and the general client state that the change depends on.
2. If a named schedule is used, follow SCHEDULE_OWNER and SCHEDULE_NAME and inspect every consumer before editing it. Changing a shared schedule can affect other objects. Keep the edit to the window and the attributes that are meant to change.
3. Inspect other jobs that use this window or its groups. Review overlapping windows, priorities, projected next openings, and resource-plan consequences. Only one window can be in effect at a time. A quiet workload hour by itself does not establish a usable Scheduler opportunity.
4. Choose a time when the target window is closed. Disable it without forcing dependencies. If disabling fails because jobs depend on the window, review those dependencies and the plan. Do not add force => TRUE simply to bypass the error.
5. Edit only the intended attributes with DBMS_SCHEDULER.SET_ATTRIBUTE. Use SET_ATTRIBUTE_NULL where restoring an actual null value is required. Verify the complete stored configuration before proceeding.
6. Re-enable the window only if it was enabled before and the change retains that state. Leave an originally disabled window disabled. An enabled window can open at its next matching time, so review the next occurrence before enabling.
7. Inspect the next complete cycle and apply the acceptance criteria. Retain the actual window times, job outcomes, task coverage, and workload measurements.

Oracle's maintenance guide uses the explicit disable–edit–enable sequence. The general Scheduler documentation also describes automatic disable/alter/re-enable behavior for an enabled window. An explicitly disabled edit makes the verification boundary clear. Altering a window while it is open does not change that active occurrence. The new values take effect at a subsequent opening. Do not force-close a production window or stop the Scheduler to remove one overlap.

Useful dependency inventory, after substituting the exact target name:

SELECT owner, job_name, schedule_owner, schedule_name,
       enabled, state, stop_on_window_close
FROM dba_scheduler_jobs
WHERE schedule_owner = 'SYS'
  AND (schedule_name = 'MAINT_TEST'
       OR schedule_name IN (
         SELECT window_group_name
         FROM dba_scheduler_wingroup_members
         WHERE window_name = 'MAINT_TEST'))
ORDER BY owner, job_name;

This query is a starting inventory of jobs that use the window or a group that contains it. It is not a complete proof that a change has no other effects.

See Automated maintenance and window configuration.

Verify actual behavior and retain a backout

Use a date range that covers the complete selected cycle, including jobs that can start before the batch and run into it. Inspect finished jobs and actual window occurrences:

SELECT client_name, window_name, job_status,
       job_start_time, job_duration, job_error, job_info
FROM dba_autotask_job_history
ORDER BY job_start_time DESC
FETCH FIRST 20 ROWS ONLY;

SELECT window_name, req_start_date, actual_start_date,
       window_duration, actual_duration, additional_info
FROM dba_scheduler_window_details
ORDER BY actual_start_date DESC
FETCH FIRST 20 ROWS ONLY;

The latest 20 rows are an initial sample. Filter and widen the capture for the actual cycle. Correlate the window name and occurrence time, rather than joining every historical run by name alone. Job history records finished jobs. Running jobs, missing history, and retention gaps require additional evidence. ACTUAL_DURATION records an actual window duration. DURATION in the window catalog is the current configured value. Compare application measurements with a representative baseline and inspect the required maintenance work before accepting the change.

If the criteria fail, execute the prepared reversal while the window is closed. Disable the target, restore the captured calendar, duration, and any other changed properties, restore client or membership settings only if they changed, then restore the original enabled state. Verify the catalog and the following cycle. Administrative changes need explicit reversal. ROLLBACK does not restore them.

For the duration edit above, the exact reversal is:

BEGIN
  DBMS_SCHEDULER.DISABLE('SYS.MAINT_TEST');
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => 'SYS.MAINT_TEST',
    attribute => 'DURATION',
    value     => NUMTODSINTERVAL(30,'MINUTE'));
END;
/

Re-run the catalog and membership queries. Require 30 minutes, the original calendar, ENABLED = FALSE, ACTIVE = FALSE, and no maintenance membership. There is no ENABLE call, because the captured state was disabled.

See Finished automated-maintenance jobs and Actual window details.

Quiz

1. Does creating a Scheduler window automatically add it to automatic maintenance?

Yes, if its name contains MAINT Yes, if it repeats daily No; maintenance-group membership and client configuration also matter Yes, if its duration is an hour

2. Why does the example use a future reference date and immediately disable the new window?

New windows are always disabled New windows start enabled, so the future opening leaves time to disable this one A future date automatically removes maintenance membership Disabling deletes the object

3. What should happen to a window that was disabled before the approved duration edit?

Enable it as a standard final step Drop and recreate it Leave it disabled and verify the stored properties Add it to the maintenance group

4. Recent work took 35 minutes and a proposed 45-minute window fits a quiet hour. What does that establish?

All future maintenance must finish The batch slowdown is solved The proposal provides 10 minutes of planned headroom; actual completion and impact still need checking Resource Manager provides an absolute completion guarantee

5. Which evidence supports accepting an operational scheduling change?

Only the current catalog duration Actual next-cycle times, job outcomes, required coverage and workload results compared with criteria The absence of an immediate package error A window name that sounds appropriate

https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily