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

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

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

Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get next blog updates !