SYS@PROD5> alter table sh.customers
2 ilm add policy row store compress advanced row
3 after 30 days of no modification;
alter table sh.customers
*
ERROR at line 1:
ORA-14646: Specified alter table operation involving compression cannot be
performed in the presence of usable bitmap indexes
检查索引，确实有BITMAP：
SYS@PROD5> select index_name,index_type from dba_indexes where table_name='CUSTOMERS';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
CUST_LNAME_IX NORMAL
CUST_EMAIL_IX NORMAL
CUSTOMERS_PK NORMAL
CUST_ACCOUNT_MANAGER_IX NORMAL
CUST_UPPER_NAME_IX FUNCTION-BASED NORMAL
SYS_IL0000073551C00022$$ LOB
SYS_IL0000073551C00023$$ LOB
CUSTOMERS_PK NORMAL
CUSTOMERS_MARITAL_BIX BITMAP
解决办法：
删除索引，或者修改位图索引为UNUSABLE
SYS@PROD5> alter index sh.CUSTOMERS_MARITAL_BIX unusable;
SYS@PROD5> select index_name,index_type,status from dba_indexes where table_name='CUSTOMERS';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
CUST_LNAME_IX NORMAL VALID
CUST_EMAIL_IX NORMAL VALID
CUSTOMERS_PK NORMAL VALID
CUST_ACCOUNT_MANAGER_IX NORMAL VALID
CUST_UPPER_NAME_IX FUNCTION-BASED NORMAL VALID
SYS_IL0000073551C00022$$ LOB VALID
SYS_IL0000073551C00023$$ LOB VALID
CUSTOMERS_PK NORMAL VALID
CUSTOMERS_MARITAL_BIX BITMAP UNUSABLE
之后再去执行成功:
SYS@PROD5> alter table sh.customers
2 ilm add policy row store compress advanced row
3 after 30 days of no modification;
Table altered.
之后，重建UNUSABLE 索引
SYS@PROD5> alter index sh.CUSTOMERS_MARITAL_BIX rebuild;
SYS@PROD5> select index_name,index_type,status from dba_indexes where table_name='CUSTOMERS';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
CUST_LNAME_IX NORMAL VALID
CUST_EMAIL_IX NORMAL VALID
CUSTOMERS_PK NORMAL VALID
CUST_ACCOUNT_MANAGER_IX NORMAL VALID
CUST_UPPER_NAME_IX FUNCTION-BASED NORMAL VALID
SYS_IL0000073551C00022$$ LOB VALID
SYS_IL0000073551C00023$$ LOB VALID
CUSTOMERS_PK NORMAL VALID
CUSTOMERS_MARITAL_BIX BITMAP VALID
--策略有效
SELECT policy_name, policy_type, enabled FROM USER_ILMPOLICIES;
POLICY_NAME POLICY_TYPE ENA
------------------------------ ------------- ---
P1 DATA MOVEMENT YES
P41 DATA MOVEMENT YES