Unable to extend temp segment by nnn in tablespace TEMP **MASTER RECORD**

• Article Type: General
• Product: Aleph
• Product Version: 20, 21, 22, 23

Description:
You get the messages below in building an Oracle index at the end of an indexing job, in trying to do util a/17/2 to build the Oracle index, or in some other context.

Examples:

CREATE INDEX xxx01.Z0102_ID1 ON xxx01.Z0102 (Z0102_REC_KEY_1 ASC)
ERROR at line 1:
ORA-01652: unable to extend temp segment by 6400 in tablespace TEMP

or

CREATE UNIQUE INDEX xxx01.Z98_ID ON xxx01.Z98 (Z98_REC_KEY ASC)
ERROR at line 1:
ORA-01652: unable to extend temp segment by 64 in tablespace TEMP

Resolution:
(It may seem that the TEMP tablespace is pretty much empty, but if you monitor the freespace as the index build is running, you will see that it rapidly becomes exhausted.)

This message indicates the need to do one of two things:

1. util o/14/9 ("Clean Temporary Tablespace Free Storage") (This script coalesces the freespace in the TEMP which tends to become highly fragmented.)   [Note:  Jan 23, 2018:  it seems this isn't working.  Instead do "add more files" as described below in #2]  

or

2. add more files to the TEMP tablespace (util o/13/3).


You may want to try #1 first and, if that fails, add the datafiles, as described in #2.

After doing this, you should be able to use util a/17/2 to build the Oracle index. If util a/17/2 fails because of duplicates, you could try doing util a/17/18.