728x90
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
Detail
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.
그렇다면 Temp 를 사용하는 경우다..
내가 가장 많이 알고 있는게 Sort 할 경우인데, 그 외에도 아래와 같다.
(Create , Drop 할때도 생길 수 있음)
|
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.
|
댓글