Automated Maintenance Tasks in Oracle 11g

Oracle Database 11g comes with three predefined automatic maintenance tasks:

  • The Automatic Optimizer Statistics Collection task collects statistics for all objects with no or stale statistics.
  • The Automatic Segment Advisor task provides advice on which database segments have free space that can be reclaimed.
  • Automatic SQL Tuning Advisor task, which examines SQL statement performance and makes SQL profile recommendations to improve the statements.

You can query the DBA_AUTOTASK_OPERATION to find out the names and status of the automatic tasks in your database. as shown in the following example:

These tasks are enabled by default.

All automated maintenance tasks are scheduled to run during the Oracle Scheduler maintenance window. The Oracle Scheduler creates a job when the window (period) starts. After the automated task job completed, the Oracle Scheduler automatically drops that job.

You can view all the predefined maintenance windows by querying the DBA_AUTOTASK_SCHEDULE view:

You can disable any of the tasks by using the DBMS_AUTO_TASK_ADMIN package. If you want to disable all the automated tasks, issue the following command::

If you want to enable all the automated tasks, issue the following command:

You can also disable (or enable) a specific task. For example, you can issue the following command to disable “gather_stats_prog” task:

You can also use the “Automated Maintenance Tasks” screen of Enterprise Manager to manage these automated tasks.

Please share
  •  
  •  
  •  
  •  
  •  
  •  

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.

4 Comments

  1. Nante

    Thats all fine.
    But where can I see the success from EXEC dbms_auto_task_admin.disable/enable ?
    I was in trouble that autotask jobs doesn’t executed (DBA_AUTOTASK_JOB_HISTORY was empty) even though STATUS columns in various views like DBA_AUTOTASK_TASK, DBA_AUTOTASK_OPERATION, DBA_AUTOTASK_CLIENT, DBA_SCHEDULER_WINDOWS showed ENABLED/TRUE !
    By the way, this happened after a full database import from a 10g dump.

    • Hasan Yardımcı

      Hi ,

      Optimizer Statistics Collection May Appear to be “Stuck”. This case is written ID 1320246.1.

      It may help you.

      BR
      Hasan.

  2. En Lopes

    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 Comment

Your email address will not be published. Required fields are marked *

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