col OBJECT_NAME for a30 col SUBOBJECT_NAME for a40 SELECT SUBSTR(OBJECT_NAME,1,20) OBJECT_NAME, SUBSTR(SUBOBJECT_NAME,1,20) SUBOBJECT_NAME, TRACK_TIME, SEGMENT_WRITE, FULL_SCAN, LOOKUP_SCAN FROM V$HEAT_MAP_SEGMENT;
createtable t1( t1_id number, t1_date date ) partition by range(t1_date) ( partition t1_date_p1 values less than (to_date('20180601', 'yyyymmdd')) tablespace tbs1, partition t1_date_p2 values less than (to_date('20180701', 'yyyymmdd')) tablespace tbs1, partition t1_date_p3 values less than (to_date('20180901', 'yyyymmdd')) tablespace tbs2, partition t1_date_p4 values less than (to_date('20181201', 'yyyymmdd')) tablespace tbs2, partition t1_date_p5 values less than (to_date('20190101', 'yyyymmdd')) tablespace tbs3, partition t1_date_p6 values less than (to_date('20190301', 'yyyymmdd')) tablespace tbs3 );
altertable t1 modify partition t1_date_p3 ilm addpolicy tier to tbs1 readonly segment after6 months ofnoaccess; altertable t1 modify partition t1_date_p4 ilm addpolicy tier to tbs1 readonly segment after6 months ofnoaccess; altertable t1 modify partition t1_date_p5 ilm addpolicy tier to tbs2 readonly segment after3 months ofnoaccess; altertable t1 modify partition t1_date_p6 ilm addpolicy tier to tbs2 readonly segment after3 months ofnoaccess; altertable t1 ilm addpolicyrow store compress basic segment after3 months ofnoaccess;
column policy_name format a20 column object_owner format a15 column object_name format a15 column subobject_name format a20 select policy_name, object_owner, object_name, subobject_name, object_type, inherited_from, enabled, deleted from user_ilmobjects;
POLICY_NAME OBJECT_OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE INHERITED_FROM ENABLED DELETED -------------------- --------------- --------------- -------------------- ------------------------------------------------------ ------------------------------------------------------------ ---------- --------- P6 XB T1 T1_DATE_P3 TABLEPARTITIONPOLICYNOT INHERITED YES NO P7 XB T1 T1_DATE_P4 TABLEPARTITIONPOLICYNOT INHERITED YES NO P8 XB T1 T1_DATE_P5 TABLEPARTITIONPOLICYNOT INHERITED YES NO P9 XB T1 T1_DATE_P6 TABLEPARTITIONPOLICYNOT INHERITED YES NO P10 XB T1 TABLEPOLICYNOT INHERITED YES NO P10 XB T1 T1_DATE_P1 TABLEPARTITIONTABLE YES NO P10 XB T1 T1_DATE_P2 TABLEPARTITIONTABLE YES NO P10 XB T1 T1_DATE_P3 TABLEPARTITIONTABLE YES NO P10 XB T1 T1_DATE_P4 TABLEPARTITIONTABLE YES NO P10 XB T1 T1_DATE_P5 TABLEPARTITIONTABLE YES NO P10 XB T1 T1_DATE_P6 TABLEPARTITIONTABLE YES NO
禁用和删除ADO策略
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
/* 禁用(删除)表中的某个ADO策略*/ ALTERTABLE t1 ILM DISABLEPOLICY P10; ALTERTABLE t1 ILM DELETEPOLICY P10;
/* 禁用(删除)表中的全部ADO策略 */ ALTERTABLE t1 ILM DISABLE_ALL; ALTERTABLE t1 ILM DELETE_ALL;
/* 禁用(删除)分区中的某个ADO策略 */ ALTERTABLE t1 MODIFY PARTITION t1_date_p3 ILM DISABLEPOLICY P6; ALTERTABLE t1 MODIFY PARTITION t1_date_p3 ILM DELETEPOLICY P6;
/* 禁用(删除)分区中的全部ADO策略 */ ALTERTABLE t1 MODIFY PARTITION t1_date_p4 ILM DISABLE_all; ALTERTABLE t1 MODIFY PARTITION t1_date_p4 ILM DELETE_ALL;
指定段级别压缩和存储层
1 2 3 4 5 6 7 8 9 10 11 12 13
段级别压缩目前只试用于EXADATA
ALTERTABLE t1 ILM ADDPOLICY COMPRESS FOR ARCHIVE HIGH SEGMENT AFTER6 MONTHS OFNO MODIFICATION; ALTERTABLE t1 ILM ADDPOLICY * ERROR at line1: ORA-64307: Exadata Hybrid Columnar Compression isnot supported for tablespaces on this storagetype
/* 增加存储层策略 */ ALTERTABLE sales_ado ILM ADDPOLICY TIER TO my_low_cost_tablespace;
指定行级别的压缩层
HCC只适用EXADATA
1 2 3 4 5 6 7 8 9 10 11 12 13 14
ALTERTABLE t1 ILM ADDPOLICYCOLUMN STORE COMPRESS FOR QUERY ROW AFTER30 DAYS OFNO MODIFICATION; ALTERTABLE t1 * ERROR at line1: ORA-64307: Exadata Hybrid Columnar Compression isnot supported for tablespaces on this storagetype
ALTERTABLE t1 ILM ADDPOLICYROW STORE COMPRESS ADVANCED ROW AFTER60 DAYS OFNO MODIFICATION;