Database maintenance tasks

Cyrille Modiano

OCP Oracle DBA in Montreal at Desjardins

Latest posts by Cyrille Modiano (see all)

oracle maintenance

The default maintenance window is not a good fit for every database, by default the maintenance window start at 10 PM and run for 4 hours during the week and start at 6 AM and last for 20 hours during the week-end. A different window exists for each day of the week and all the windows are grouped inside the MAINTENANCE_WINDOW_GROUP. It is possible to alter each window independently or to create a single window for all days of the week.

The following maintenance tasks are performed during the maintenance window:

  • Optimizer statistics (OPTIMIZER_STATS)
  • Segment advisor (SEGMENT_ADVISOR)
  • Health Monitor (HEALTH_MONITOR)

And the default window group named MAINTENANCE_WINDOW_GROUP is associated with a DEFAULT_MAINTENANCE_PLAN which has the following configuration:

Consumer Group/subplanLevel 1Maximum Utilization Limit

In this plan 75% of the resources are affected to sessions created by SYS or SYSTEM user. If these resources are not used by the SYS_GROUP then they are transfered to the other groups. As you can see there is also a restriction for the automatic maintenance tasks, they cannot use more that 90% of the resources.

here is what you is configured by default, independently of the management pack configured and the edition you use. This configuration is from a standard edition database with control_management_pack_access set to NONE.

Maintenance example

In my case the database i’m working on is a 24/7 database which has less activity between 10 AM and 12 AM every day. I will create a new maintenance window named MORNING_WINDOW:

Then we need to add the maintenance window to the MAINTENANCE_WINDOW_GROUP:

Let’s remove the default windows from MAINTENANCE_WINDOW_GROUP, we don’t need them anymore.

Here is the new configuration:

Deactivating some maintenance tasks

If you, like me and are in Standard Edition you have not use of the SQL Tuning advisor for example, you have no right to use it, but it runs in your maintenance window for nothing, let’s disable it:

If you want to deactivate the task only for a specific window you can use :

If you want to deactivate all the tasks, use the following command:

Altering the default maintenance windows

If you choose to keep the default maintenance windows and just change one of them to adapt it to you needs you can use the DBMS_SCHEDULER.SET_ATTRIBUTE prodcedure.

Change the interval :

Change the duration of the Window:

If you need more information you can have a look at the oracle documentation:

Managing Automated Database Maintenance Tasks

Good maintenance 🙂

6 thoughts on “Database maintenance tasks

  1. Thanks Cyrille for nice article.
    The docs you pointed too are 12c, I believe your article is true too for 11g, the version at which the framework ‘autotask’ appeared for maintenace tasks

    1. Hi Foued,

      Thanks for your support and your feedback, you are absolutely right, these views appeared in 11g even if some of these task were also available in 10g.


  2. I want to create an Oracle DBMS Job that runs every week day (not on weekends) from 09:00 to 20:00 every 10 min.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.