成名男子专栏Welcome to weijsh's Blog
ORA-14185: incorrect physical attribute specified for this index partition
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


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

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>

文章分类: ORA-错误集锦
前篇(06-04-15): SOA的几个关键特性
后篇(06-04-16): 3721总裁周鸿祎----认同感就是凝聚力

最新回复(1件)
主题/内容 作者/日时
Re: ORA-14185: incorrect physical attribute specified for this index partition

^_^ buy wOW GOld
EURO starting at $29.99, and make sure to check for rare and epic items on your server. Enjoy!

fgd
08-02-21 15:16

发表评论
标题:
称呼:
内容:

引用链接
您可以按照以下步骤引用本文.本站收到您的引用通知后, 将自动链接您的文章, 以方便别人阅览 .
1. 启动您自己的博客管理页面, 并进入发表新文章的画面, 输入文章的内容. (如果您是ITPUB的博客请点这里.)
2. 复制下面虚线框里的连接字串, 把它们粘贴到您的文章中, 按照您的喜好修改一下表示文字.
3. 确认您选择了"发送引用通知"的选项.
4. 发表您的文章.
好啦, 您的文章就可以被自动链接到本站啦.

« 七月 2008 »
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31      

ORACLE分类
  • 虎口脱险
  • 辰风的专栏
  • 人生就是如此
  • eygle's life
  • piner的专栏
  • 左岸年华(ocp8i)

  • 名人分类
  • 咖啡小驻--系分


  • Creative Commons License 本站全部著作均采用CC授权. Plog 1.0 is powered by: plogworld.net.
    Itpub BLOG is provided by: itpub.net.
    This temlate(named Happy-Life's SunShine) is designed by lodge@itpub(肥猫猫).