How to Manage Automatic Data Optimization (ADO)

In this last posting on Automatic Data Optimization, we will look at issues related to monitoring and managing Automatic Data Optimization (ADO). 

Displaying Automatic Data Optimization (ADO) Policies

Several dictionary views are available to verify configured policies.

DBA_ILMPOLICIES provides the list of all policies and their respective ENABLED status:Automatic Data Optimization - 1The DBA_ILMDATAMOVEMENTPOLICIES view contains information specific to data movement-related attributes:Automatic Data Optimization - 2Another example of the DBA_ILMDATAMOVEMENTPOLICIES view:Automatic Data Optimization -3In the following example, a row-level compression policy P342 is created to automatically compress the blocks of a table after 30 days of no modification:

Automatic Data Optimization -4

Preparing for Evaluation and Execution

Change the interval for ADO policies evaluation which is specified in minutes:Automatic Data Optimization - 5Immediate ADO task execution on a set of objects.

There are two ways for ADO execution with DBMS_ILM package:

– Immediate ADO task execution on a set of objects.Automatic Data Optimization -6             The execute command must be entered on a single line.

– Or, you can customize the set of objects before ADO task execution.

Stop execution:Automatic Data Optimization -7

Customizing the Evaluation and Execution

Use the DBMS_ILM.PREVIEW_ILM procedure to view the results of evaluation of ADO policies on a set of objects.  The scope of evaluation can be one of the two:

– A whole schema


The evaluation selects all ADO policies in the current schema.  This is the default.

– The whole database


The evaluation selects all ADO policies in the database.

Then use the EXECUTE_ILM_TASK procedure to perform immediate scheduling of ADO execution on this set of objects.

Monitoring the Evaluation and Execution

View the results of the ADO tasks and jobs executions in the following views:



Automatic Data Optimization -8

Managing the ADO Policies

Policies can be enabled, disabled, and deleted.

Enable all policies on an object:Automatic Data Optimization -9

Disable all policies on an object:Automatic Data Optimization -10

Delete all policies on an object:Automatic Data Optimization -11

Be aware of a key issue.  When creating a new table using the CREATE TABLE AS SELECT COMMAND, the new table does not inherit the ADO policies.

Turn off ADO without disabling or deleting all policies.  You need the SYSDBA privilege to use this package:Automatic Data Optimization -12

Turn ADO back on for all policies:Automatic Data Optimization -13

View ADO parameters:Automatic Data Optimization -14

The value of 1  means that ADO is enabled, 2 means that ADO is disabled.

Stop activity tracking in session:Automatic Data Optimization -15

Stop activity tracking at the instance level:Automatic Data Optimization -16

Clean up heat map statistics:Automatic Data Optimization -17

Automatic Data Optimization is a very powerful capability for Information Lifecycle Management (ILM).  There are a number of other tools which we will discuss in future postings.  Next up is In-Database Archiving.  Future articles will look at Temporal History and Temporal Validity.

Type to search

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.