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.
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