Oracle Automatic Data Optimization (ADO)
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Have you viewed the Library's main Table of Contents? If not ... [Click Here] ... scroll down the page and you will find links to the largest collection of Oracle Database demos on the net.
Purpose Work is still in progress on this page
Description We just won the acronym sweepstakes with a number of new capabilities added to our favorite database each with its very own acronym and no shortage of confusion as to how all of these pieces work together.

ILM stands for Information Lifecycle Management and to quote the Oracle docs:

"To implement your ILM strategy, you use Heat Maps in the database to track data access and modification. You can also use Automatic Data Optimization (ADO) to automate the compression and movement of data between different tiers of storage within the database. The DBMS_ILM package supports immediate evaluation or execution of Automatic Data Optimization (ADO) related tasks. The package supports the following two ways for scheduling ADO actions.
  • A database user schedules immediate ADO policy execution on a set of objects.
  • A database user views the results of evaluation of ADO policies on a set of objects.
The user then adds or deletes objects to this set and reviews the results of ADO policy evaluation again. The user repeats this step to determine the set of objects for ADO execution. The user can then perform immediate scheduling of ADO actions on this set of objects.

The following procedures support the two usage modes. Before describing the procedures, we introduce the notion of an ADO task as an entity that helps to track a particular evaluation or (an evaluation and execution) of ADO policies. A particular ADO task could be in one of the following states.
  • Inactive
  • Active
  • Completed
Use the links below to explore the components of Oracle ADO.
Dependencies
DBA_HEAT_MAP_TOP_OBJECTS DBA_ILMEVALUATIONDETAILS DBA_ILMRESULTS
DBA_HEAT_MAP_TOP_TABLESPACES DBA_ILMPOLICIES DBA_ILMTASKS
DBA_HEAT_MAP_SEGMENT    
 
Header
Tablespace level policy ALTER TABLE [<schema_name.>]<table_name> ILM ADD POLICY <policy_name>;
ALTER TABLE uwclass.servers ILM ADD POLICY tier to ilmtbs;
Compress a table after there have been no modifications for one week ALTER TABLE [<schema_name.>]<table_name> ILM ADD POLICY ROW STORE COMPRESS
ADVANCED <ROW | SEGMENT> AFTER <integer> DAYS OF NO MODIFICATIONS;
ALTER TABLE uwclass.servers ILM ADD POLICY ROW STORE COMPRESS
ADVANCED segment AFTER 7 DAYS OF NO MODIFICATIONS;
Compress rows after one week of not being updated ALTER TABLE uwclass.servers ILM ADD POLICY ROW STORE COMPRESS
ADVANCED row AFTER 7 DAYS OF NO MODIFICATIONS;
  CREATE TABLESPACE tier1_ts DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/tier1.dbf' SIZE 10M AUTOEXTEND ON NEXT 5M;
CREATE TABLESPACE tier2_ts DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/tier2.dbf' SIZE 10M AUTOEXTEND ON NEXT 5M;
CREATE TABLESPACE tier3_ts DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/tier3.dbf' SIZE 50M AUTOEXTEND ON NEXT 5M;

CREATE TABLE order_hdr(
order_no NUMBER NOT NULL,
order_date DATE NOT NULL,
order_note VARCHAR2(500))
PARTITION BY RANGE (order_date)
(
PARTITION orders_2016_q3 VALUES LESS THAN (TO_DATE('01/10/2016', 'DD/MM/YYYY')) TABLESPACE tier3_ts,
PARTITION orders_2016_q4 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')) TABLESPACE tier3_ts,
PARTITION orders_2017_q1 VALUES LESS THAN (TO_DATE('01/04/2017', 'DD/MM/YYYY')) TABLESPACE tier2_ts
ILM ADD POLICY TIER TO tier3_ts READ ONLY SEGMENT AFTER 3 MONTHS OF NO ACCESS,
PARTITION orders_2017_q2 VALUES LESS THAN (TO_DATE('01/07/2017', 'DD/MM/YYYY')) TABLESPACE tier2_ts
ILM ADD POLICY TIER TO tier3_ts READ ONLY SEGMENT AFTER 3 MONTHS OF NO ACCESS,
PARTITION orders_2017_q3 VALUES LESS THAN (TO_DATE('01/10/2017', 'DD/MM/YYYY')) TABLESPACE tier1_ts
ILM ADD POLICY TIER TO tier2_ts READ ONLY SEGMENT AFTER 1 MONTHS OF NO ACCESS,
PARTITION orders_2017_q4 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')) TABLESPACE tier1_ts
ILM ADD POLICY TIER TO medium_storage_ts READ ONLY SEGMENT AFTER 2 MONTHS OF NO ACCESS)
ILM ADD POLICY ROW STORE COMPRESS BASIC SEGMENT AFTER 1 MONTHS OF NO ACCESS;
 
Header
   
 
   

Related Topics
Built-in Functions
Built-in Packages
DBMS_HEAT_MAP
DBMS_ILM
DBMS_ILM_ADMIN
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved