为什么我收到“ORA-01429：索引组织表”？当尝试将列从 VARCHAR2(200) 修改为 VARCHAR2(1000) 时？

发布于 2024-11-03 04:01:54 字数 340 浏览 1 评论 0原文

它当前是数据库中的 VARCHAR2(200)，但需要将其提升为 VARCHAR(1000)，因此我尝试运行此脚本：

ALTER TABLE CONTRACTOR MODIFY
(
    NOTE VARCHAR2(1000)
);

Oracle 给了我这个：

ORA-01429：索引组织表：没有数据段来存储溢出行片段

这是一个 10g 数据库。有什么想法吗？我可以创建一个重复列，复制数据，然后删除旧列，但在执行此操作之前我想先知道这个错误是什么。

原文

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:

ALTER TABLE CONTRACTOR MODIFY
(
    NOTE VARCHAR2(1000)
);

Oracle gives me this:

ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一念一轮回 2024-11-10 04:01:54

根据文档，您需要指定溢出段对于可能太大而无法放入单个块的行。

考虑（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

但是，当您指定溢出段时：

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

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):

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

回复收藏 0 原文

-黛色若梦 2024-11-10 04:01:54

你可以运行sql：
更改表表名添加溢出；
然后运行你的sql

回复收藏 0 原文

娇妻 2024-11-10 04:01:54

我发现了这个排名很高的页面
当我用谷歌搜索“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");

我的问题得到了以下修复：
https://community.oracle.com/thread/2244949?tstart=0

OVERFLOW 必须像“OVERFLOW”一样用双引号引起来。我想在这里分享它，是为了其他在分割 OVERFLOW 分区时面临相同 ORA 异常的 stackoverflow 粉丝的利益。

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 partition in a Oracle 11G table.

*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

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.

回复收藏 0 原文

~没有更多了~