要将一个分区索引挪到指定的表空间,首先检查一下: Segment Name Size In MB Tablespace Name Segment Type 直接使用alter index ... rebuild tablespace ...肯定是不行的,你会收到ORA-14086报错,例如: 要将一个分区索引挪到指定的表空间,首先检查一下: Segment Name Size In MB Tablespace Name Segment Type 直接使用alter index ... rebuild tablespace ...肯定是不行的,你会收到ORA-14086报错,例如: 我们需要使用: SQL> 关于ORA-14185 的解释: 一般来说,带有下面这些的选项的rebuild index语句会遇到ORA-14185错误: 而带有下面这些的选项的rebuild index语句则不会遇到ORA-14185错误: 例如: Index altered. SQL>
--------------------------- ---------- -------------------- ------------------
MISC16.PK_SERVSTAT 6 INDEX_HIS02 INDEX PARTITION
MISC16.PK_SERVSTAT 6 INDEX_HIS03 INDEX PARTITION
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
--------------------------- ---------- -------------------- ------------------
MISC16.PK_SERVSTAT 6 INDEX_HIS02 INDEX PARTITION
MISC16.PK_SERVSTAT 6 INDEX_HIS03 INDEX PARTITION
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
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:
logging
nologging
initrans 10
maxtrans 10
compress
nocompress
storage (next 1m)
storage (buffer_pool default)
parallel
noparallel
pctfree 10
compute statistics
online
tablespace system
SQL> ALTER INDEX PK_SERVSTAT REBUILD PARTITION IDX_SERVSTAT_P3 tablespace DATA_DYNAMIC;
前篇(06-04-15): SOA的几个关键特性
后篇(06-04-16): 3721总裁周鸿祎----认同感就是凝聚力
最新回复(1件)
| 主题/内容 | 作者/日时 |
|---|---|
|
Re: ORA-14185: incorrect physical attribute specified for this index partition
^_^ buy wOW GOld |
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 | |||
