12c ILM資料生命週期管理

首先，需要開啟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;