ORA-01630 Max # Extents(%) reached in temp segment

Rupesh Ghubade

Error:
ORA-01630 Max # Extents(%) reached in temp segment in tablespace %

Cause:
This max extents error occurs when the current number of extents in a temporary segment equals the maximum number of extents specified by the MAXEXTENTS storage option for the object(i.e. the segment itself or the tablespace where the segment is being created in),or by the maximum number of extents allowable for the db_block_size, whichever is smaller, and an attempt is made to add another extent.

In short, Max_extent parameter current value has reached to its maximum value i.e. 249.

TABLESPACE_NAME MAX_EXTENTS CONTENTS PCT_INCREASE
------------------------- ----------- --------- ------------
PT_INDX 249 PERMANENT 0

Block Size KTEMXEXT
----------- --------
4096 (4K) 249

Solution:
Max extents can be set for an object and the tablespace using the MAXEXTENTS option of the storage clause.

SQL> alter tablespace PT_INDX storage (maxextents unlimited);