其中所有原因都可能导致索引在oracle中进入不可用状态[关闭](Which all reasons can cause index to go into unusable state in orac

其中所有原因都可能导致索引在oracle中进入不可用状态[关闭](Which all reasons can cause index to go into unusable state in oracle [closed])

我们正在使用oracle DB。 在某些索引的“ALL_INDEXES”表中，状态值显示为“UNUSABLE”。 我们已经观察到将表从压缩移动到未压缩或反之亦然。 但是我们还没有执行表的移动，但仍然显示某些索引无法使用。 有人可以解释所有原因。

We are using oracle DB. In the "ALL_INDEXES" table for some of the indexes the status value is showing as "UNUSABLE". This we have observed when we move tables from compressed to uncompressed or vice versa. But we have not perform moving of the tables and still it showing for some of the indexes unusable. Can someone explain which all reasons are there.

最满意答案

我们不是在SO上创建列表，但是让我们说问题是“什么可能导致索引无法使用？”。 我们的想法是，任何通过“批量”ddl操作触及表（分区）段的内容都会使索引无效。

例如，如果截断分区或删除分区，则全局索引将设置为不可用。 （ 编辑 ：这里我们也可以用直接路径计算sqlldr-ing，如这里所讨论的）

另一个原因是某人将其设置为unusable ;

We are not creating lists on SO, but let's say the question is "What may caused the index go unusable?". The idea is that anything that touches the table(partition) segment with a "bulk" ddl operation, invalidates the index.

For example, if you truncate a partition or drop it, the global index will be set unusable. (Edit: Here we can count also sqlldr-ing with direct path as discussed here)

Another reason would be that someone set it unusable;