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:
There are two ways for ADO execution with DBMS_ILM package:
– Or, you can customize the set of objects before ADO task 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:
Policies can be enabled, disabled, and deleted.
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.
The value of 1 means that ADO is enabled, 2 means that ADO is disabled.
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.