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.