ora-29861 全文索引异常报错

今天有人问我ora-29861问题应该如何解决。如何造成的。他的存储报错报这个错误（封装了内部的错误code）

ora-29861: 域索引标记为loading/failed/unusable

 

[oracle@ADG1 ~]$ oerr ora 29861

29861, 00000, "domain index is marked LOADING/FAILED/UNUSABLE"

// *Cause: An attempt has been made to access a domain index that is

//         being built or is marked failed by an unsuccessful DDL

//         or is marked unusable by a DDL operation.

// *Action: Wait if the specified index is marked LOADING

//          Drop the specified index if it is marked FAILED

//          Drop or rebuild the specified index if it is marked UNUSABLE.

解决方案：

1：等待有问题的索引变成loading状态

2：删除被标记为失败的索引

3：删除或者重建被标记为unusable的索引

 

1：查看全文索引

select idx_name,idx_status from ctxsys.ctx_indexes

2：查询异常的索引

select owner,index_name,PARAMETERS,domidx_opstatus,TABLE_NAME from all_indexes where domidx_opstatus<>'VALID'

Note：检查无效的索引

3：重建同步

alter index IND_TITLE rebuild online parameters('sync')

Note：当时处理坐到次步骤已经ok。没有失败的索引

如果第三步失败，则强制删除索引

DROP INDEX IND_TITLE force

 

Note：在创建完全文索引之后，请检查确认索引是否正常被建立。