首先，需要打开heat map，用来跟踪数据的访问情况

SQL> alter system set heat_map = on; System altered.

测试数据

drop table p purge; create table p (id int, name varchar2(150)) partition by range (id) (partition p1 values less than (100), partition p2 values less than (200)) tablespace t1; insert into p values(20, 'ulooiuk'); insert into p values(80, 'vf sfdsafgaf'); insert into p values(90, 'AXSAXSD'); insert into p values(120, 'ulooiuk'); insert into p values(180, 'vf sfdsafgaf'); insert into p values(190, 'AXSAXSD'); create index i_p_id on p(id) local; create index i_p_name on p(name) global; commit;

SQL> select partition_name, (select name from v$datafile where file# = file_id) name, extent_id, block_id, blocks from dba_extents e where segment_name = 'P'; PARTITION_NAME       NAME                                                EXTENT_ID   BLOCK_ID     BLOCKS -------------------- -------------------------------------------------- ---------- ---------- ---------- P1                   /u01/oradata/orcl/t1.dbf                                    0       1280       1024 P2                   /u01/oradata/orcl/t1.dbf                                    0        128       1024

设置策略

简单测试一个数据的压缩和旧迁移至其它表空间。

SQL> ALTER TABLE p   2    ILM ADD POLICY COMPRESS SEGMENT   3    AFTER 1 DAYS OF NO MODIFICATION; Table altered. SQL> ALTER TABLE p   2    ILM ADD POLICY   3    TIER TO ts2; Table altered.

SQL> alter table p ilm add policy compress for archive high segment after 1 days of no modification; alter table p ilm add policy compress for archive high segment after 1 days of no modification * ERROR at line 1: ORA-38342: heat map not enabled

查看已定义的策略

SQL> select SUBSTR(policy_name,1,24), POLICY_TYPE, TABLESPACE, enabled from user_ilmpolicies; SUBSTR(POLICY_NAME,1,24) POLICY_TYPE   TABLESPACE                     ENABLED ------------------------ ------------- ------------------------------ ------- P1                       DATA MOVEMENT                                YES P2                       DATA MOVEMENT                                YES

SQL> select * from USER_ILMDATAMOVEMENTPOLICIES; POLICY_NAM ACTION_TYPE SCOPE   COMPRESSION_LEVEL              TIER_TABLE TIER_STAT CONDITION_TYPE         CONDITION_DAYS CUSTOM_FUNCTION ---------- ----------- ------- ------------------------------ ---------- --------- ---------------------- -------------- ------------------------------ P1         COMPRESSION SEGMENT ARCHIVE HIGH                                        LAST MODIFICATION TIME              1 P2         STORAGE     SEGMENT                                TS2                                                       0

执行后，数据段就被压缩了

SQL> select table_name, partition_name, compression from user_tab_partitions; TABLE_NAME PARTITION_NAME            COMPRESS ---------- ------------------------------ -------- P        P2                      ENABLED P        P1                      ENABLED

数据段都换了一个地方进行压缩

SQL> select partition_name, (select name from v$datafile where file# = file_id) name, extent_id, block_id, blocks from dba_extents e where segment_name = 'P'; PARTITION_NAME                 NAME                                 EXTENT_ID   BLOCK_ID     BLOCKS ------------------------------ -------------------------------------------------- ---------- ---------- ---------- P1                      /u01/oradata/orcl/t1.dbf                        0        5120       1024 P2                      /u01/oradata/orcl/t1.dbf                        0        6144       1024

索引都有效

SQL> select index_name, status from user_indexes where table_name = 'P'; INDEX_NAME                               STATUS -------------------------------------- -------- I_P_NAME                               VALID I_P_ID                                 N/A SQL> select partition_name, status from user_ind_partitions where index_name = 'I_P_ID'; PARTITION_NAME           STATUS ------------------------ -------- P1             USABLE P2             USABLE

ILM参数

SQL> select * from dba_ilmparameters; NAME                                               VALUE -------------------------------------------------- ---------------------------------------- ENABLED                                            1   是否允许 RETENTION TIME                                     30  ILM job历史保留时间（天） JOB LIMIT                                          2   同时能有多少ILM job运行。计算方法是“JOB LIMIT参数值*实例数*每个实例的CPU数” EXECUTION MODE                                     2   online还是offline EXECUTION INTERVAL                                 15  执行间隔，单位分钟。 TBS PERCENT USED                                   85  当表空间使用率达到这个值，ILM认为它要满了，会尽可能把数据移走。使它的空闲空间达到“TBS PERCENT FREE” TBS PERCENT FREE                                   25   POLICY TIME                                        0    8 rows selected.

修改参数

begin dbms_ilm_admin.customize_ilm(dbms_ilm_admin.tbs_percent_used, 85); dbms_ilm_admin.customize_ilm(dbms_ilm_admin.tbs_percent_free, 25); end; /

也可以手工执行ILM任务

set serverout on declare     tid number; begin     dbms_ilm.execute_ilm(tid);     dbms_output.put_line(tid); end; /

然后查看执行结果

col job_name for a30 col comments for a30

col COMPLETION_TIME for a30

col STATISTICS for a30 SQL> select * from USER_ILMRESULTS where task_id = 3;    TASK_ID JOB_NAME       JOB_STATE           START_TIME         COMPLETION_TIME        COMMENTS ---------- ------------------------------ ----------------------------------- ------------------------------ ------------------------------ ------------------------------ STATISTICS ------------------------------    3 ILMJOB132        COMPLETED SUCCESSFULLY        09-MAR-15 10.10.21.314846 AM   09-MAR-15 10.11.27.596074 AM    3 ILMJOB134        COMPLETED SUCCESSFULLY        09-MAR-15 10.10.21.319984 AM   09-MAR-15 10.11.24.671375 AM

禁用、删除ILM策略

alter table p ilm disable policy p1; alter table p ilm delete policy p1;