ORA-1652

ORA 1652 unable to extend temp segment by %s in tablespace %s

 Applies to:

Oracle Server - Enterprise Edition - Version 7.0.16.0 to 8.1.5.0 [Release 7.0 to 8.1.5]
Information in this document applies to any platform.

Purpose

 Provide OERR information for ORA-1652

Details

Error:  ORA-1652
Text:   unable to extend temp segment by %s in tablespace %s
------- -----------------------------------------------------------------------
Cause:  Failed to allocate an extent for temp segment in tablespace.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
        files to the tablespace indicated or create the object in another
        tablespace.

*** Important: The notes below are for experienced users - See Note:22080.1


Explanation:
        This error is fairly self explanatory - we cannot get enough space for a temporary segment.
        The size reported in the error message is the number of contiguous free Oracle blocks that cannot be found in the listed tablespace.

  NOTE: A "temp segment" is not necessarily a SORT segment in a temporary tablespace.
        It is also used for temporary situations while creating or dropping objects like tables and indexes in permanent tablespaces.
        eg: When you perform a CREATE INDEX a TEMP segment is created to hold what will be the final permanent index data.
              This TEMP segment is converted to a real INDEX segment in the dictionary at the end of the CREATE INDEX operation.
              It remains a temp segment for the duration of the CREATE INDEX operation and so failures to extend it report ORA-1652 rather than an INDEX related space error.
      

A TEMPORARY segment may be from:

A SORT	Used for a SELECT or for DML/DDL
CREATE INDEX	The index create performs a SORT in the users default TEMP tablespace and ALSO uses a TEMP segment to build the final index in the INDEX  tablespace. Once the index build is complete the segment type is changed.
CREATE PK CONSTRAINT
ENABLE CONSTRAINT
CREATE TABLE	New tables start out as TEMPORARY segments.    Eg: If MINEXTENTS is > 1 or you issue CREATE table as SELECT.
Accessing a GLOBAL TEMPORARY TABLE	When you access a global temporary table a TEMP segment is instantiated to hold the temporary data.

It is worth making sure the TEMP tablespace PCTINCREASE is 0 and  that it has a sensible (large) storage clause to prevent fragmentation.
        For TEMPORARY temp tablespaces make sure both INITIAL and NEXT are  set to large values as extent sizes are taken from the NEXT clause and not the INITIAL clause.