敬业的IT人 >> 数据库 >> 其他数据库 >> ORA-14185: incorrect physical attribute specifie

ORA-14185: incorrect physical attribute specifie

敬业的IT人 互联网 佚名 2008-1-8 16:08:20

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

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>

浏览URL 进入讨论组讨论。
粤ICP备06119539号
Copyright CiscoSky.Org,Some Rights Reserved.
Email：me1228#tom.com