记一次删除oracle高水位线问题

最近在oracle生产上删除数据以及高水位时，发现一些问题， 在此记录一下

业务要求是，备份一年前的数据到另一个新表，同时删除当前表一年前的数据，给旧数据“搬家”，当然是先执行insert…select，再来delete，后面再删除高水位。

什么是“高水位”，官方叫high water mark，在进行DML的时候，高水位线会增长，进行delete的时候并不会降低高水位线，oracle在进行select的时候，会从高水位线以下的块去查询，不及时释放已删除数据的空间，当然会影响查询效率。

就好比电脑在删除文件的时候，硬盘空间并没有变少，要手动去回收站删除才能真正释放一个道理

知道这么一回事后，执行以下两个命令

-- 打开行移动功能
alter table TABLE_NAME enable row movement;
-- 删除高水位，cascade的意思是收缩表的同时收缩索引
alter table TABLE_NAME shrink space cascade;

第一次执行的时候报了这个错

异常信息：ORA-10662: Segment has long columns

原因是上述sql不支持对存在long类型字段的表进行压缩，
于是手动改成CLOB类型（Long类型也会逐渐被BLOB、CLOB和BFILE取代，建议能不用long就别用）

alter table TABLE_NAME MODIFY FIELD CLOB;

修改完字段类型，再次执行上面两条sql，又报错…
这次是

异常信息：ORA-10631: SHRINK clause should not be specified for this object

度娘告诉我，原来是存在函数式索引

查询索引sql如下

select i.uniqueness ,i.index_type,t.column_name , t.* from dba_ind_columns t,dba_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = 'table_name'

既然如此，删除索引，用普通索引创建

DROP index IDX_AGGREGATE_ORDER_DEL_STATE;
create index index_name on table_name (field_name)

再次执行删除高水位sql，执行成功！