Configure Automatic Data Optimization, In-Row Archiving, and Temporal Validity

Go back to OCM 12c Preparation Project 

Hands On Lab – Index

[1] Automatic Data Optimization
[2] In-Row Archiving
[3] Temporal Validity

[1] Automatic Data Optimization

#To implement your ILM strategy, you can use Heat Map and Automatic Data 
Optimization (ADO).

You can use Heat Map in Oracle Database to track data access and 
modification.

Heat Map provides data access tracking at the segment-level and 
data modification tracking at the segment and row level. 
You can enable this functionality with the HEAT_MAP initialization parameter.

ALTER SYSTEM SET HEAT_MAP = ON;

-> When Heat Map is enabled, all accesses are tracked by the in-memory 
activity tracking module. Objects in the SYSTEM and SYSAUX tablespaces 
are not tracked.

ALTER SYSTEM SET HEAT_MAP = OFF;

-> When Heat Map is disabled, accesses are not tracked by the in-memory 
activity tracking module. 
The default value for the HEAT_MAP initialization parameter is OFF.

The HEAT_MAP initialization parameter also enables and disables 
Automatic Data Optimization (ADO). 
For ADO, Heat Map must be enabled at the system level.

#Displaying Heat Map Tracking Data With Views

V$HEAT_MAP_SEGMENT 
-> Displays real-time segment access information.
USER_HEAT_MAP_SEGMENT 
-> Displays the latest segment access time for all segments owned by the user
USER_HEAT_MAP_SEG_HISTOGRAM 
-> Displays segment access information for segments owned by the user.
DBA_HEATMAP_TOP_OBJECTS 
-> Displays heat map information for the top 10000 objects by default. 
DBA_HEATMAP_TOP_TABLESPACES 
-> Displays heat map information for the top 100 tablespaces.

#DBMS_HEAT_MAP includes one set of APIs that externalize heat maps at 
various levels of storage such as block, extent, segment, object, and 
tablespace; 
DBMS_HEAT_MAP.OBJECT_HEAT_MAP
DBMS_HEAT_MAP.TABLESPACE_HEAT_MAP 
DBMS_HEAT_MAP.EXTENT_HEAT_MAP

Using Automatic Data Optimization
----------------------------------
To implement your ILM strategy, you can use Automatic Data Optimization 
ADO) to automate the compression and movement of data between different 
tiers of storage within the database. The functionality includes the ability 
to create policies that specify different compression levels for each tier, 
and to control when the data movement takes place.

Managing Policies for Automatic Data Optimization

-> You can specify policies for ADO at the row, segment, and tablespace 
level when creating and altering tables with SQL statements.

-> By specifying policies for ADO, you can automate data movement between 
different tiers of storage within the database. These policies also enable 
you to specify different compression levels for each tier, and to control 
when the data movement takes place. The scope of policies for ADO can be 
specified as SEGMENT, ROW, or GROUP.

-> The ILM clauses of the SQL CREATE and ALTER TABLE statements enable you 
   to create, delete, enable or disable a policy for ADO. 
-> An ILM policy clause determines the compression or storage tiering policy 
   and contains additional clauses.

-> When you create a table, you can add a new policy for ADO. You can alter 
the table to add more policies or to enable, disable, or delete existing 
policies. You can add policies to an entire table or a partition of a table. 
ILM ADO policies are given a system-generated name, such P1, P2, ... Pn.

Note:
A segment level policy executes only one time. After the policy executes 
successfully, it is disabled and is not evaluated again. However, you can 
explicitly enable the policy again. A row level policy continues to execute 
and is not disabled after a successful execution.

#Creating a Table With an ILM ADO Policy

$ sql
SQL> select cdb from v$database;

CDB
---
NO

SQL> show parameter heat

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
heat_map string ON

CREATE TABLE sales_ado 
(PROD_ID NUMBER NOT NULL,
CUST_ID NUMBER NOT NULL, 
TIME_ID DATE NOT NULL, 
CHANNEL_ID NUMBER NOT NULL,
PROMO_ID NUMBER NOT NULL,
QUANTITY_SOLD NUMBER(10,2) NOT NULL,
AMOUNT_SOLD NUMBER(10,2) NOT NULL )
PARTITION BY RANGE (time_id)
( PARTITION sales_q1_2012 VALUES LESS THAN (TO_DATE('01-APR-2012','dd-MON-yyyy')),
PARTITION sales_q2_2012 VALUES LESS THAN (TO_DATE('01-JUL-2012','dd-MON-yyyy')),
PARTITION sales_q3_2012 VALUES LESS THAN (TO_DATE('01-OCT-2012','dd-MON-yyyy')),
PARTITION sales_q4_2012 VALUES LESS THAN (TO_DATE('01-JAN-2013','dd-MON-yyyy')) )
ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT 
AFTER 12 MONTHS OF NO ACCESS;


USER_ILMPOLICIES 
-> View the existing ILM ADO polices

SELECT SUBSTR(policy_name,1,24) POLICY_NAME, policy_type, enabled FROM USER_ILMPOLICIES;


#Adding ILM ADO Policies

/* Add a row-level compression policy after 30 days of no modifications */
ALTER TABLE sales MODIFY PARTITION sales_q1_2002 
ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW 
AFTER 30 DAYS OF NO MODIFICATION;

/* Add a segment level compression policy for data after 6 months of no changes */
ALTER TABLE sales MODIFY PARTITION sales_q1_2001 
ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT 
AFTER 6 MONTHS OF NO MODIFICATION;

/* Add a segment level compression policy for data after 12 months of no access */
ALTER TABLE sales MODIFY PARTITION sales_q1_2000 
ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT 
AFTER 12 MONTHS OF NO ACCESS;

/* Add storage tier policy to move old data to a different tablespace */
/* that is on low cost storage media */
ALTER TABLE sales MODIFY PARTITION sales_q1_1999 
ILM ADD POLICY TIER TO my_low_cost_sales_tablespace;

/* View the existing polices */
SELECT SUBSTR(policy_name,1,24) POLICY_NAME, policy_type, enabled 
FROM USER_ILMPOLICIES;

#Disabling and Deleting ILM ADO Policies

/* You can disable or delete an ADO policy in a table with the following */
ALTER TABLE sales_ado ILM DISABLE POLICY P1;
ALTER TABLE sales_ado ILM DELETE POLICY P1;

/* You can disable or delete all ADO policies in a table with the following */
ALTER TABLE sales_ado ILM DISABLE_ALL;
ALTER TABLE sales_ado ILM DELETE_ALL;

/* You can disable or delete an ADO policy in a partition with the following */
ALTER TABLE sales MODIFY PARTITION sales_q1_2002 ILM DISABLE POLICY P2;
ALTER TABLE sales MODIFY PARTITION sales_q1_2002 ILM DELETE POLICY P2;

/* You can disable or delete all ADO policies in a partition with the following */
ALTER TABLE sales MODIFY PARTITION sales_q1_2000 ILM DISABLE_ALL;
ALTER TABLE sales MODIFY PARTITION sales_q1_2000 ILM DELETE_ALL;

#Specifying Segment-Level Compression and Storage Tiering With ADO

Example : illustrates how to create policies for ADO to enforce a 
compression and storage tiering policy on the sales_ado table, 
reflecting the following business requirements:
1. Bulk Load Data
2. Run OLTP workloads
3. After six months with no updates, compress for Archive High
4. Move to low cost storage

/* Add a segment level compression policy after 6 months of no changes */
ALTER TABLE sales_ado 
ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT 
AFTER 6 MONTHS OF NO MODIFICATION;

/* Add storage tier policy */
ALTER TABLE sales_ado 
ILM ADD POLICY TIER TO my_low_cost_tablespace;

SELECT SUBSTR(policy_name,1,24) POLICY_NAME, policy_type, enabled 
FROM USER_ILMPOLICIES;

#Specifying Row-Level Compression Tiering With ADO
Row-level compression policies for ADO operate only on database blocks in 
which all the rows qualify based on the policy condition for compression.

Example: creates a policy that compresses the rows of the table sales_ado for 
         OLTP after 30 days without modification of the data:

ALTER TABLE sales_ado 
ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW 
AFTER 30 DAYS OF NO MODIFICATION;

SELECT SUBSTR(policy_name,1,24) POLICY_NAME, policy_type, enabled 
FROM USER_ILMPOLICIES;

#Managing ILM ADO Parameters

You can customize your ADO environment with ILM ADO parameters
You can display the parameters with the DBA_ILMPARAMETERS view.

SQL> SELECT NAME, VALUE FROM DBA_ILMPARAMETERS;

BEGIN
DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.TBS_PERCENT_USED,80);
DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.TBS_PERCENT_FREE,20);
END;
/

EXEC DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.RETENTION_TIME,15);

You can use the PL/SQL DBMS_ILM and DBMS_ILM_ADMIN packages for advanced 
policy management and customization to implement more complex ADO scenarios 
and control when policies are actively moving and compressing data. 
With the PL/SQL DBMS_ILM and DBMS_ILM_ADMIN packages, you can manage 
ILM activities for ADO so that they do not negatively impact important 
production workloads. Database compatibility must be set to a minimum of 
12.0 to use these packages.

#Using Views to Monitor Policies for ADO
USER_ILMDATAMOVEMENTPOLICIES 
-> Displays information specific to data movement related attributes of an 
   ILM policy for ADO.
USER_ILMTASKS 
-> Contains information about all ILM tasks for ADO. 
USER_ILMEVALUATIONDETAILS
-> Displays details on policies considered for a particular task
USER_ILMOBJECTS 
-> Displays all the objects and policies for ADO in the database. 
USER_ILMPOLICIES 
-> Displays details about all the policies for ADO in the database.
USER_ILMRESULTS 
-> Displays information about data movement-related jobs for ADO in the database.
DBA_ILMPARAMETERS 
-> Displays information about ADO-related parameters.

#Limitations and Restrictions With ADO and Heat Map
-> ADO and Heat Map are not supported with a multitenant container database.

-> Partition-level ADO and compression are supported for Temporal Validity 
except for row-level ADO policies that would compress rows that are past 
their valid time (access or modification).

-> Partition-level ADO and compression are supported for in-database archiving 
   if partitioned on the ORA_ARCHIVE_STATE column.
-> Custom policies (user-defined functions) for ADO are not supported if 
   the policies default at the tablespace level.
-> ADO does not perform checks for storage space in a target tablespace 
   when using storage tiering.
-> ADO is not supported on tables with object types or materialized views.
-> ADO is not supported with index-organized tables or clusters.
-> ADO concurrency (the number of simultaneous policy jobs for ADO) depends 
   on the concurrency of the Oracle scheduler. 
-> If a policy job for ADO fails more than two times, then the job is marked 
   disabled and the job must be manually enabled later.
-> Policies for ADO are only run in the Oracle Scheduler maintenance windows. 
   Outside of the maintenance windows all policies are stopped. The only 
   exceptions are those jobs for rebuilding indexes in ADO offline mode.
-> ADO has restrictions related to moving tables and table partitions.

#Controlling the Validity and Visibility of Data in Oracle Database#
You can control the validity and visibility of data in Oracle Database 
with In-Database Archiving and Temporal Validity.

[2] In-Row Archiving

-> In-Database Archiving enables you to archive rows within a table by 
   marking them as inactive.
-> These inactive rows are in the database and can be optimized using 
   compression, but are not visible to an application.
-> The data in these rows is available for compliance purposes if needed 
   by setting a session parameter.
-> With In-Database Archiving you can store more data for a longer period 
   of time within a single database, without compromising application performance. 
-> Archived data can be compressed to help improve backup performance, 
   and updates to archived data can be deferred during application upgrades 
   to improve the performance of upgrades.
-> To manage In-Database Archiving for a table, you must enable 
   ROW ARCHIVAL for the table and manipulate the ORA_ARCHIVE_STATE hidden 
   column of the table.
-> Optionally, you specify either ACTIVE or ALL for the 
   ROW ARCHIVAL VISIBILITY session parameter.

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

CREATE TABLE employees_indbarch
(employee_id number(6) not null,
first_name varchar2(20),last_name varchar2(25) not null,
email varchar2(25) not null,
phone_number varchar2(20),
hire_date date not null,
job_id varchar2(10) not null,
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),department_id number(4))
ROW STORE;

SQL> INSERT INTO employees_indbarch(employee_id, first_name, last_name, email,
hire_date, job_id, salary, manager_id, department_id)
VALUES (251, 'Scott', 'Tiger', 'scott.tiger@example.com',SYSDATE, 'IT_PROG', 50000, 103, 60);

1 row created.

SQL> INSERT INTO employees_indbarch(employee_id, first_name, last_name, email,
hire_date, job_id, salary, manager_id, department_id)
VALUES (252, 'Jane', 'Lion', 'jane.lion@example.com',SYSDATE, 'IT_PROG', 50000, 103, 60);

1 row created.

SQL> commit;

Commit complete.

/* Show all the columns in the table, including hidden columns */
SELECT SUBSTR(COLUMN_NAME,1,22) NAME, SUBSTR(DATA_TYPE,1,20) DATA_TYPE, COLUMN_ID AS COL_ID,
SEGMENT_COLUMN_ID AS SEG_COL_ID, INTERNAL_COLUMN_ID AS INT_COL_ID, HIDDEN_COLUMN, CHAR_LENGTH 
FROM USER_TAB_COLS WHERE TABLE_NAME='EMPLOYEES_INDBARCH';

NAME DATA_TYPE COL_ID SEG_COL_ID INT_COL_ID HID CHAR_LENGTH
---------------------- -------------------- ---------- ---------- ---------- --- -----------
ORA_ARCHIVE_STATE VARCHAR2 1 1 YES 4000
EMPLOYEE_ID NUMBER 1 2 2 NO 0
FIRST_NAME VARCHAR2 2 3 3 NO 20
LAST_NAME VARCHAR2 3 4 4 NO 25
EMAIL VARCHAR2 4 5 5 NO 25
PHONE_NUMBER VARCHAR2 5 6 6 NO 20
HIRE_DATE DATE 6 7 7 NO 0
JOB_ID VARCHAR2 7 8 8 NO 10
SALARY NUMBER 8 9 9 NO 0
COMMISSION_PCT NUMBER 9 10 10 NO 0
MANAGER_ID NUMBER 10 11 11 NO 0
DEPARTMENT_ID NUMBER 11 12 12 NO 0


SQL> /* The default value for ORA_ARCHIVE_STATE is '0', which means active */
SELECT employee_id, ORA_ARCHIVE_STATE FROM employees_indbarch;SQL>

EMPLOYEE_ID ORA_ARCHIVE_STATE
----------- ------------------
251 0
252 0

SQL> /* Insert a value into ORA_ARCHIVE_STATE to set inactive */
UPDATE employees_indbarch SET ORA_ARCHIVE_STATE = '20' WHERE employee_id = 252;SQL>

1 row updated.

SQL> commit;

Commit complete.

SQL> SELECT employee_id, ORA_ARCHIVE_STATE FROM employees_indbarch;

EMPLOYEE_ID ORA_ARCHIVE_STATE
----------- ------------------
251 0

SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

Session altered.

SQL> SELECT employee_id, ORA_ARCHIVE_STATE FROM employees_indbarch;

EMPLOYEE_ID ORA_ARCHIVE_STATE
----------- ------------------
251 0
252 20

[3] Temporal Validity

Temporal Validity enables you to track time periods for real world validity.
Valid times can be set by users and applications for data, and data can be 
selected by a specified valid time, or a valid time range.

Applications often note the validity (or effectivity) of a fact recorded 
in a database with dates or timestamps that are relevant to the management 
of a business.

For example, the hire-date of an employee in a human resources (HR) 
application, which determines the effective date of coverage 
in the insurance industry, is a valid date. This date is in contrast to 
the date or time at which the employee record was entered in the database. 
The former temporal attribute (hire-date) is called the valid time (VT) 
while the latter (date entered into the database) is called the transaction 
time (TT). While the valid time is usually controlled by the user, 
the transaction-time is system-managed.

For ILM, the valid time attributes can signify when a fact is valid in 
the business world and when it is not Using valid time attributes, a query 
could just show rows that are currently valid, while not showing rows 
that contains facts that are not currently valid, such as a closed order 
or a future hire.

Concepts that are integral to valid time temporal modeling include:

Valid time
-> This is a user-defined representation of time. Examples of a valid time 
include project start and finish dates, and employee hire and termination dates.

Tables with valid-time semantics
-> These tables have one or more dimensions of user-defined time, 
each of which has a start and an end.

Valid-time flashback queries
-> This is the ability to do as-of and versions queries using a 
valid-time dimension.

A valid-time period consists of two date-time columns specified in the 
table definition. You can add a valid-time period by explicitly adding 
columns, or the columns can be created automatically. A valid-time period 
can be added during the create table or alter table process.

To support session level visibility control for temporal table queries, 
the DBMS_FLASHBACK_ARCHIVE PL/SQL package provides the ENABLE_AT_VALID_TIME 
procedure. To execute the procedure, you need the required system and 
object privileges.

The following PL/SQL procedure sets the valid time visibility as of the 
given time.

EXECUTE DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time ('ASOF', '31-DEC-12 12.00.01 PM');

PL/SQL procedure successfully completed.

The following PL/SQL procedure sets the visibility of temporal data to 
currently valid data within the valid time period at the session level.

SQL> EXECUTE DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time('CURRENT');

PL/SQL procedure successfully completed.

The following procedure sets the visibility of temporal data to the full 
table, which is the default temporal table visibility.

SQL> EXECUTE DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time('ALL');

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_FLASHBACK_ARCHIVE.DISABLE_ASOF_VALID_TIME;

PL/SQL procedure successfully completed.

#Creating a Table With Temporal Validity

SQL> CREATE TABLE employees_temp (
employee_id NUMBER(6) NOT NULL, first_name VARCHAR2(20), last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25) NOT NULL, phone_number VARCHAR2(20), hire_date DATE NOT NULL,
job_id VARCHAR2(10) NOT NULL, salary NUMBER(8,2), commission_pct NUMBER(2,2),
manager_id NUMBER(6), department_id NUMBER(4),
PERIOD FOR emp_track_time);

SQL> desc employees_temp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)


SQL> SELECT SUBSTR(COLUMN_NAME,1,22) NAME, SUBSTR(DATA_TYPE,1,28) DATA_TYPE, COLUMN_ID AS COL_ID,
SEGMENT_COLUMN_ID AS SEG_COL_ID, INTERNAL_COLUMN_ID AS INT_COL_ID, HIDDEN_COLUMN 
FROM USER_TAB_COLS WHERE TABLE_NAME='EMPLOYEES_TEMP';

NAME DATA_TYPE COL_ID SEG_COL_ID INT_COL_ID HID
---------------------- ---------------------------- ------ ---------- ---------- ---
EMP_TRACK_TIME_START TIMESTAMP(6) WITH TIME ZONE 1 1 YES
EMP_TRACK_TIME_END TIMESTAMP(6) WITH TIME ZONE 2 2 YES
EMP_TRACK_TIME NUMBER 3 YES
EMPLOYEE_ID NUMBER 1 3 4 NO
FIRST_NAME VARCHAR2 2 4 5 NO
LAST_NAME VARCHAR2 3 5 6 NO
EMAIL VARCHAR2 4 6 7 NO
PHONE_NUMBER VARCHAR2 5 7 8 NO
HIRE_DATE DATE 6 8 9 NO
JOB_ID VARCHAR2 7 9 10 NO
SALARY NUMBER 8 10 11 NO
COMMISSION_PCT NUMBER 9 11 12 NO
MANAGER_ID NUMBER 10 12 13 NO
DEPARTMENT_ID NUMBER 11 13 14 NO

/* Insert/update/delete with specified values for time columns */
INSERT INTO employees_temp(emp_track_time_start, emp_track_time_end, 
employee_id, first_name,last_name, email, hire_date, job_id, salary, 
manager_id, department_id) 
VALUES (TIMESTAMP '2009-06-01 12:00:01 Europe/Paris', 
TIMESTAMP '2012-11-30 12:00:01 Europe/Paris', 251, 'Scott', 'Tiger',
'scott.tiger@example.com', DATE '2009-05-21', 'IT_PROG', 50000, 103, 60);

INSERT INTO employees_temp(emp_track_time_start, emp_track_time_end, 
employee_id, first_name,last_name, email, hire_date, job_id, salary, 
manager_id, department_id) 
VALUES (TIMESTAMP '2009-06-01 12:00:01 Europe/Paris', 
TIMESTAMP '2012-12-31 12:00:01 Europe/Paris', 252, 'Jane', 'Lion',
'jane.lion@example.com', DATE '2009-06-11', 'IT_PROG', 50000, 103, 60); 


UPDATE employees_temp set salary = salary + salary * .05 
WHERE emp_track_time_start <= TIMESTAMP '2009-06-01 12:00:01 Europe/Paris';

SELECT employee_id, SALARY FROM employees_temp;

EMPLOYEE_ID SALARY
----------- ----------
251 52500
252 52500

/* No rows are deleted for the following statement because no records */
/* are in the specified track time. */
DELETE employees_temp 
WHERE emp_track_time_end < TIMESTAMP '2001-12-31 12:00:01 Europe/Paris';

0 rows deleted.

/* Show rows that are in a specified time period */
SELECT employee_id FROM employees_temp 
WHERE emp_track_time_start > TIMESTAMP '2009-05-31 12:00:01 Europe/Paris' 
AND emp_track_time_end < TIMESTAMP '2012-12-01 12:00:01 Europe/Paris';

EMPLOYEE_ID
-----------
251


/* Show rows that are in a specified time period */
SELECT employee_id FROM employees_temp AS OF PERIOD FOR 
emp_track_time TIMESTAMP '2012-12-01 12:00:01 Europe/Paris';

EMPLOYEE_ID
-----------
252 

SELECT employee_id,EMP_TRACK_TIME,EMP_TRACK_TIME_START,EMP_TRACK_TIME_END 
FROM employees_temp;

EMPLOYEE_ID EMP_TRACK_TIME EMP_TRACK_TIME_START EMP_TRACK_TIME_END 
----------- -------------- ------------------------------------------ --------------------------------------------
251 429804672 01-JUN-09 12.00.01.000000 PM EUROPE/PARIS 30-NOV-12 12.00.01.000000 PM EUROPE/PARIS 
252 429804672 01-JUN-09 12.00.01.000000 PM EUROPE/PARIS 31-DEC-12 12.00.01.000000 PM EUROPE/PARIS


#Limitations and Restrictions With In-Database Archiving and Temporal Validity

-> Temporal validity is not supported with a multitenant container database.

-> ILM is not supported with OLTP table compression for Temporal Validity. 
Segment-level ILM and compression is supported if partitioned on the 
end-time columns.

-> ILM is not supported with OLTP table compression for in-database archiving. 
Segment-level ILM and compression is supported if partitioned on the 
ORA_ARCHIVE_STATE column.

#Implementing an ILM System Manually Using Partitioning
Reference - Example 5-11 Manually implementing an ILM system
......
......
CREATE OR REPLACE FUNCTION ilm_seehist
(oowner IN VARCHAR2, ojname IN VARCHAR2)
RETURN VARCHAR2 AS con VARCHAR2 (200);
BEGIN
IF SYS_CONTEXT('USERENV','CURRENT_USER') = 'SH'
THEN -- sees all data
con:= '1=1';
ELSIF SYS_CONTEXT('USERENV','CURRENT_USER') = 'PM'
THEN -- sees only data for 2007
con := 'time_id > ''31-Dec-2006'''; 
ELSE
-- others nothing
con:= '1=2';
END IF;
RETURN (con);
END ilm_seehist;
/

##Managing ILM Heat Map and ADO with Oracle Enterprise Manager##

You can manage Heat Map and Automatic Data Optimization with 
Oracle Enterprise Manager Cloud Control.

======================
Patch to Documentation
======================
Data Warehousing and Business Intelligence - > Database VLDB and Partitioning Guide
-> 5 Managing and Maintaining Time-Based Information
--> Implementing an ILM Strategy With Heat Map and ADO 
--> Using Automatic Data Optimization
--> Controlling the Validity and Visibility of Data in Oracle Database
--> Using In-Database Archiving
--> Using Temporal Validity

Thank you for visiting this blog…

Advertisements