In this last posting on Automatic Data Optimization, we will look at issues related to monitoring and managing Automatic Data Optimization (ADO).
Several dictionary views are available to verify configured policies.
DBA_ILMPOLICIES provides the list of all policies and their respective ENABLED status:
The DBA_ILMDATAMOVEMENTPOLICIES view contains information specific to data movement-related attributes:
Another example of the DBA_ILMDATAMOVEMENTPOLICIES view:
In 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:
Change the interval for ADO policies evaluation which is specified in minutes:
Immediate 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.
The execute command must be entered on a single line.
– Or, you can customize the set of objects before ADO task execution.
Stop 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
Attribute ILM_SCOPE => DBMS_ILM.SCOPE_SCHEMA
The evaluation selects all ADO policies in the current schema. This is the default.
– The whole database
Attribute ILM_SCOPE => DBMS_ILM.SCOPE_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.
View the results of the ADO tasks and jobs executions in the following views:
DBA_ILMTASKS
DBA_ILMEVALUATIONDETAILS
DBA_ILMRESULTS
Policies can be enabled, disabled, and deleted.
Enable all policies on an object:
Disable all policies on an object:
Delete all policies on an object:
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:
Turn ADO back on for all policies:
View ADO parameters:
The value of 1 means that ADO is enabled, 2 means that ADO is disabled.
Stop activity tracking in session:
Stop activity tracking at the instance level:
Clean up heat map statistics:
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.
