Database Reference
In-Depth Information
Implementing the ILM policy is done by invoking the alter table command for table T1:
SQL> alter table martin.t1
2 ilm add policy
3 row store compress basic segment
4 after 3 months of no access;
Table altered.
The criteria to apply the ILM policy can be based on access, modification, or creation. In this example the policy
is applicable to the table partition. The interval can be any number of days, months, or years. In the above example the
data in segments (=partitions) with no access will be compressed using BASIC compression. If you are lucky enough
to be on an Exadata system you can request Hybrid Columnar Compression as well. You can view policies attached to
a table in the view
dba_ilmpolicies
. More detail for a policy is available in the view
dba_ilmdatamovepolicies
.
Fast forward three months and you can see that the ILM processes in the database start reviewing the access to
the segments more closely. The view
dba_ilmevaluationdetails
lists the outcome of the evaluation. The system
periodically checks ILM policies against the heat map. This process is externalized in
dba_ilmtasks
. You could join
dba_ilmtasks
to
dba_ilmevaluationdetails
for more detail about when a given task has been executed. In the above
example the following evaluation results have been made available by the ILM engine:
SQL> select task_id,policy_name,object_name,subobject_name,
2 selected_for_execution, job_name
3 from dba_ilmevaluationdetails
4 order by policy_name,selected_for_execution;
TASK_ID POLICY_NAM OBJEC SUBOBJECT_ SELECTED_FOR_EXECUTION JOB_NAME
---------- ---------- ----- ---------- ------------------------------------------ ------------
2 P1 T1 SYS_P658 PRECONDITION NOT SATISFIED
[...]
2 P1 T1 SYS_P650 SELECTED FOR EXECUTION ILMJOB190
2 P1 T1 SYS_P648 SELECTED FOR EXECUTION ILMJOB192
These jobs are executed by the database scheduler, and you can find out about them in
dba_ilmresults
. The
outcome of the operation is visible in the
dba_tab_partitions
SQL> r
1 select partition_name, compression, compress_for
2 from DBA_TAB_PARTITIONS
3* where table_name = 'T1'
PARTITION_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------------------
SYS_P621 DISABLED
[...]
SYS_P632 ENABLED BASIC
SYS_P660 ENABLED BASIC
SYS_P670 ENABLED BASIC
[...]
54 rows selected.
SQL>
Search WWH ::
Custom Search