重建索引報ORA-14086錯誤

xueshancheng發表於2022-05-18

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 = '<index_name>';

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

SQL> alter index <Index_name> rebuild partition <Partition_name>;


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.



來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/69996316/viewspace-2894999/,如需轉載,請註明出處,否則將追究法律責任。

相關文章