在实际的生产环境中，进行自动表压缩，一个大分区约100G，压缩时先move到别的表空间，然后再move回来。
执行过程中报ORA-14327: Some index [sub]partitions could not be rebuilt，经检查，发现，分区数据已经move back到原表空间，只有分区索引状态为unusable，检查alter日志，如下

Mon Aug 01 15:59:26 2016
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP_NIOS
DBMS_INDEX_UTL: Error executing ALTER INDEX "NIOSPMDB"."IDX_TPD_RADIO_HUAWEI_ADJ_Q" REBUILD PARTITION "TPD_RADIO_HUAWEI_ADJ_Q06" NOPARALLEL
DBMS_INDEX_UTL: Caused by ORA-01652: unable to extend temp segment by 128 in tablespace TEMP_NIOS
Mon Aug 01 15:59:51 2016

与压缩日志中记录的报错时间基本吻合2016-08-01 16:00:03，从sr查找相关资料
数据库版本为：
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

CAUSE

This issue is caused by Bug 16025489

SOLUTION

The bug is fixed on:

11.2.0.4
12.1.0.2

To resolve the issue either :

Upgrade to the above versions wherein bug is already fixed

OR apply patch for Bug 16025489

OR apply the Workaround:

Manually rebuild unusable indexes after the ORA-14327 error

REFERENCES

BUG:16025489 - ALTER TABLE .. MOVE SUBPARTITION ... UPDATE INDEXES FAILS WITH ORA-14327

BUG:16025489：
https://support.oracle.com/epmos/faces/BugDisplay?_afrLoop=268783381045538&parent=DOCUMENT&sourceId=1923184.1&id=16025489&_afrWindowMode=0&_adf.ctrl-state=qqqdbwnhl_149

ITPub博客

alter table xx move tablespace xx update indexes faild with ORA-14327

CAUSE

SOLUTION

REFERENCES

最新文章