1 重建索引报错

SYS@test2 >alter index user02.PK_E_TAB_PAR rebuild online tablespace TBS001 parallel 8 ;

alter index user02.PK_E_TAB_PAR rebuild online tablespace TBS001 parallel 8

*

ERROR at line 1:

ORA-14086: a partitioned index may not be rebuilt as a whole

2 查看官方的报错信息，让根据分区名称进行重建

[oracle@yxjcptdb3 ~]$ oerr ora 14086

14086, 00000, "a partitioned index may not be rebuilt as a whole"

// *Cause: User attempted to rebuild a partitioned index using

// ALTER INDEX REBUILD statement, which is illegal

// *Action: Rebuild the index a partition at a time (using

// ALTER INDEX REBUILD PARTITION) or drop and recreate the

// entire index

3 查看官方文档

ORA-14086 When Rebuilding Index Of Partition Table (Doc ID 2403717.1)

APPLIES TO:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

SYMPTOMS

RDBMS version 11.2.0.4 onwards

When trying to rebuild an partition index, ORA-14086 error is spotted

"ORA-14086:a partition index may not be rebuild as a whole".

CHANGES

No changes

CAUSE

This issue is essentially a syntax error and following correct syntax resolve this ORA-14086 error.

SOLUTION

Step 1: Look-up the partition name in dba_ind_partitions view.

select Index_owner,Index_name,partition_name from dba_ind_partitions where index_name = '';

Step 2: specify the partition name in the index rebuild statement:

SQL> alter index rebuild partition ;

4 查询分区名称

OWNER SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME SEGMENT_TYPE TRUNC(SUM(BYTES)/1024/1024/1024)

-------------------- ---------------------------------------- -------------------- -------------------- -------------------- --------------------------------

user02 PK_E_TAB_PAR E_TAB_PAR20190101 TBS_01 INDEX PARTITION 13

5 根据分区名称，重新重建索引，成功，如下：

SYS@test2 >alter index user02.PK_E_TAB_PAR rebuild partition E_TAB_PAR20190101 online tablespace

TBS001 parallel 8 ;

Index altered.

重建索引报ORA-14086错误

APPLIES TO:

SYMPTOMS

CHANGES

CAUSE

SOLUTION