Why am I getting "ORA-01429: Index-Organized Table" when trying to modify column from VARCHAR2(200) to VARCHAR2(1000)?

Question

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.

Answer 1

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