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

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

Cause: No overflow segment defined.

Action: Add overflow segment

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

The maximum sizes of the columns comprising an index cannot be more than half of the block size.

When an Index-Organised Table is being created, many or all of the columns are held within the B*-Tree index. The error:

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

will occur if the maximum size of the columns held in the index part of the IOT are more than half the block size for the tablespace (or database, prior to 9i).

This problem can be overcome by specifying an overflow segment in which either some columns on long rows or all columns after a named column are held separate for the rest of the row, i.e not in the B*-Tree structure. Obviously the primary key columns must be in the index.

The downside of using an overflow segment is that performance is reduced whenever the columns in the overflow segment need to be accessed.

Alternatively, the table can be configured to reject long rows. This is done by specifying a PCTTHRESHOLD value (0-50) without an OVERFLOW clause.