The SQL statements in Example 5-4 provide examples of adding ILM policies to a partition of the existing sales table in the SH sample schemas.
Example 5-4 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;
POLICY_NAME POLICY_TYPE ENABLE
------------------------ ------------- ------
P1 DATA MOVEMENT YES
P2 DATA MOVEMENT YES
P3 DATA MOVEMENT YES
P4 DATA MOVEMENT YES
P5 DATA MOVEMENT YES