The SQL statement in Example 5-3 creates a table and adds an ILM policy.
Example 5-3 Creating a table with an ILM ADO policy
/* Create an example table with an ILM ADO policy */
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;
/* View the existing ILM ADO polices */
SELECT SUBSTR(policy_name,1,24) POLICY_NAME, policy_type, enabled
FROM USER_ILMPOLICIES;
POLICY_NAME POLICY_TYPE ENABLE
------------------------ ------------- ------
P1 DATA MOVEMENT YES