ORA-01652 错误中报出的不是Temp表空间的情况。

ORA-01652  unable to extend temp segment by %s in tablespace %s 注意这里的temp segment并不一定就是指临时表空间, 也可能是其它的表空间,见下面描述

 

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.