ORA-14185: incorrect physical attribute specified for this index partition

王朝other·作者佚名  2006-04-16
窄屏简体版  字體: |||超大  

要将一个分区索引挪到指定的表空间,首先检查一下:

Segment Name Size In MB Tablespace Name Segment Type

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

MISC16.PK_SERVSTAT 6 INDEX_HIS02 INDEX PARTITION

MISC16.PK_SERVSTAT 6 INDEX_HIS03 INDEX PARTITION

直接使用alter index ... rebuild tablespace ...肯定是不行的,你会收到ORA-14086报错,例如:

SQL> alter index MISC16TEST.PK_SERVSTAT rebuild tablespace DATA_DYNAMIC nologging;

alter index MISC16TEST.PK_SERVSTAT rebuild tablespace DATA_DYNAMIC nologging

*

ERROR at line 1:

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

SQL>

我们需要使用:

ALTER INDEX <索引名称> REBUILD PARTITION <索引分区名称> [tablespace 表空间] [nologging];

语法来重建分区索引:

SQL> ALTER INDEX MISC16TEST.PK_SERVSTAT REBUILD PARTITION IDX_SERVSTAT_P3 tablespace DATA_DYNAMIC nologging;

ALTER INDEX MISC16TEST.PK_SERVSTAT REBUILD PARTITION IDX_SERVSTAT_P3 tablespace DATA_DYNAMIC nologging

*

ERROR at line 1:

ORA-14185: incorrect physical attribute specified for this index partition

SQL>

关于ORA-14185 的解释:

Error: ORA-14185

Text: incorrect physical attribute specified for this index partition

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

Cause: unexpected option was encountered while parsing physical attributes of

a local index partition; valid options for Range or Composite Range

partitions are INITRANS, MAXTRANS, TABLESPACE, STORAGE, PCTFREE,

PCTUSED, LOGGING and TABLESPACE; but only TABLESPACE may be specified

for Hash partitions STORE IN () is also disallowed for all but

Composite Range partitions

Action: remove invalid option(s) from the list of physical attributes of an

index partition

总结:

这个 ORA-14185 其实是oracle的 Bug 1890073:

一般来说,带有下面这些的选项的rebuild index语句会遇到ORA-14185错误:

logging

nologging

initrans 10

maxtrans 10

compress

nocompress

storage (next 1m)

storage (buffer_pool default)

而带有下面这些的选项的rebuild index语句则不会遇到ORA-14185错误:

parallel

noparallel

pctfree 10

compute statistics

online

tablespace system

例如:

SQL> ALTER INDEX PK_SERVSTAT REBUILD PARTITION IDX_SERVSTAT_P3 tablespace DATA_DYNAMIC;

Index altered.

SQL>

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航