oracle 为什么在尝试将列从 VARCHAR2(200) 修改为 VARCHAR2(1000) 时会收到“ORA-01429：索引组织表”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5791298/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why am I getting "ORA-01429: Index-Organized Table" when trying to modify column from VARCHAR2(200) to VARCHAR2(1000)?
提问by oscilatingcretin
It's currently a VARCHAR2(200)in the database, but it needs to be raised to VARCHAR(1000), so I am attempting to run this script:
它目前VARCHAR2(200)在数据库中，但需要提升到VARCHAR(1000)，所以我试图运行这个脚本：
ALTER TABLE CONTRACTOR MODIFY
(
NOTE VARCHAR2(1000)
);
Oracle gives me this:
Oracle 给了我这个：
ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces
ORA-01429：索引组织表：没有数据段来存储溢出的行段
This is a 10g database. Any ideas what's up? I could create a duplicate column, copy the data over, and then drop the old column, but I would like to know what this error is first before I do that.
这是一个 10g 的数据库。有什么想法吗？我可以创建一个重复的列，复制数据，然后删除旧列，但在我这样做之前，我想先知道这个错误是什么。
回答by Vincent Malgrat
According to the documentation, you need to specify an overflow segment for rows that might be to large to fit in a single block.
根据文档，您需要为可能太大而无法放入单个块的行指定溢出段。
Consider (10.2.0.3 -- 8k blocks):
考虑（10.2.0.3 -- 8k 块）：
SQL> CREATE TABLE contractor (
2 ID NUMBER PRIMARY KEY,
3 data_1 CHAR(1000),
4 data_2 CHAR(1000),
5 data_3 CHAR(1000),
6 data_4 CHAR(1000),
7 data_5 CHAR(1000),
8 NOTE VARCHAR2(200)
9 )
10 ORGANIZATION INDEX;
ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces
However, when you specify an overflow segment:
但是，当您指定溢出段时：
SQL> CREATE TABLE contractor (
2 ID NUMBER PRIMARY KEY,
3 data_1 CHAR(1000),
4 data_2 CHAR(1000),
5 data_3 CHAR(1000),
6 data_4 CHAR(1000),
7 data_5 CHAR(1000),
8 NOTE VARCHAR2(200)
9 )
10 ORGANIZATION INDEX
11 OVERFLOW TABLESPACE USER_DATA;
Table created
回答by pahariayogi
I came across this highly ranked page when i googled 'ORA-01429'. I also faced the same exception when i was trying to split OVERFLOW partitionin a Oracle 11G table.
我在谷歌搜索“ORA-01429”时遇到了这个排名靠前的页面。当我尝试在 Oracle 11G 表中拆分 OVERFLOW 分区时，我也遇到了同样的异常。
*Incorrect*:
alter table sales SPLIT PARTITION OVERFLOW at (TO_DATE('01-FEB-2017 00:00:00', 'DD-MON-YYYY HH24:MI:SS'))
INTO (PARTITION sales_m01_2017, PARTITION OVERFLOW);
*Fixed*:
alter table sales SPLIT PARTITION OVERFLOW at (TO_DATE('01-FEB-2017 00:00:00', 'DD-MON-YYYY HH24:MI:SS'))
INTO (PARTITION sales_m01_2017, PARTITION "OVERFLOW");
I got the fix for my problem at following: https://community.oracle.com/thread/2244949?tstart=0
我在以下位置解决了我的问题：https: //community.oracle.com/thread/2244949?tstart=0
OVERFLOW has to be double quoted like "OVERFLOW". I though to share it here in the interest of other stackoverflow fans facing the same ORA exception while spliting OVERFLOW partition.
OVERFLOW 必须像“OVERFLOW”一样被双引号引用。我想在这里分享它是为了其他 stackoverflow 粉丝在拆分 OVERFLOW 分区时面临相同的 ORA 异常。
