ORA-20001: Job failed: ORA-01630: max # extents (4096) reached in temp segment in tablespace USERS

SCOPE

Application jobs fails with messages as below.

ORA-20001: Job failed

ORA-01630: max # extents (4096) reached in temp segment in tablespace USERS

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
------------------------- ----------- --------- ------------
USERS                             249 PERMANENT            0


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

Excerpt

-- Excerpt from Alert Log:

DBMS_INDEX_UTL: Error executing ALTER INDEX "SPARK"."I_SNAP$_VSDCDRBONDS2" REBUILD  NOPARALLEL

DBMS_INDEX_UTL: Caused by ORA-01630: max # extents (4096) reached in temp segment in tablespace USERS

 

SOLUTION

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

alter index SPARK.I_SNAP$_VSDCDRBONDS2 STORAGE ( MAXEXTENTS UNLIMITED);

alter tablespace USERS default storage (maxextents unlimited)

MOS  Master noteS

NOTE:1493350.1  -Master Note: Overview of Oracle Tablespace Management

NOTE:1522807.1  -Master Note: Troubleshooting Oracle Tablespace Management

NOTE:1498442.1  -Master Note: Overview of Oracle Temporary Tablespaces

Found this post interesting? Subscribe us  😊😉

"If the highest aim of a captain were to preserve his ship, he would keep it in port forever." 
                                                                                                                  —Thomas Aquinas