[Oracle] ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

업체에서 점검 중에 아래와 같은 에러 메시지를 Alert Log 에서 학인함

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.

Sort Segment 확인

-- Used_block 과 Total_block 이 같으면 temp 가 부족한 것을 확인 가능

select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment; 

아래의 명령어로 현재 temp 를 누가 사용하는지 확인 가능

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text

FROM v$session a, v$tempseg_usage b, v$sqlarea c

WHERE a.saddr = b.session_addr

AND c.address= a.sql_address

AND c.hash_value = a.sql_hash_value

ORDER BY b.tablespace, b.blocks;

TEMP 확이

SELECT * FROM DBA_TEMP_FILES;

Trace 확인 했을 때에는 GATHER_TABLE_STATS 를 확인 하였다...

흠...테이블 단위 통계 정보 수집이면...이때도 Temp 를 쓰는가 보다..

When gathering GLOBAL statistics a large amount of TEMP space is needed in order to collect the necessary information.

해결방법

Gather the statististics at the partition level rather than the table level and derive the table level statistics from the partitions.

To do this gather using granularity => 'PARTITION'.

exec dbms_stats.gather_table_stats(ownname => '<schema name>', tabname => '<table name>', granularity => 'PARTITION');

참고로 10g 이라서 아래도 참조할 부분이다.

This new value exists in 10.2.0.5 and for 10.2.0.4 after Patch 6526370 applied (see more in Note 6526370.8 Bug 6526370 - New GRANULARITY option for DBMS_STATS statistics on the table).

해결방법

1. tempfiles을 추가, 현재 파일 사이즈를 증가하거나 혹은 auto extend를 활성화

2. 쿼리/구문을 튜닝하여 sort 작업들이 디스크가 아니라 메모리에서 수행(가장중요)

1. Temp 를 하나 더 추가 

ALTER TABLESPACE TEMP ADD TEMPFILE '/ORADATA/TEMP02.DBF' SIZE 1000M AUTOEXTEND ON;

2.기본 Temp resize 

ALTER DATABASE TEMPFILE ''/ORADATA/TEMP01.DBF'' RESIZE 2000M; 

참조oracle 문서

How Do You Find Who And What SQL Is Using Temp Segments (문서 ID 317441.1)

ORA-1652 에러에 대한 문제 해결 방안 (문서 ID 1908925.1)

RAC 환경은,  Note 280578.1

참조 사이트

http://jun3.tistory.com/26