[오라클-오류] ORA-01652: unable to extend temp segment by 128 in tablespace TEMP (테이블 공간 TEMP에서 임시 세그먼트를 확장할 수 없습니다.)

[Oracle-오류] ORA-01652: unable to extend temp segment by 128 in tablespace TEMP (테이블 공간 TEMP에서 임시 세그먼트를 확장할 수 없습니다.)

오라클 오류코드인 ORA-01652은 임시 세그먼트(temp segment)를 확장할 수 없을 때 발생하는 에러입니다. 이 에러의 원인과 해결방안, 그리고 예시와 결론을 알아보겠습니다.

ORA-01652

 

[오류 내용]

ORA-01652 에러는 임시 세그먼트를 확장할 수 없을 때 발생하는 에러입니다. 

임시 세그먼트는 SQL 문장을 실행할 때 필요한 임시 공간을 할당하는 논리적인 구조입니다. 

예를 들어, 정렬(sort), 그룹화(group by), 해시 조인(hash join) 등의 작업을 수행할 때 임시 세그먼트가 사용됩니다.

 

ORA-01652 에러는 다음과 같은 형식으로 출력됩니다.
ORA-01652: unable to extend temp segment by storage_allocatedstorage_units in tablespace tablespace_name

 

 

[오류 원인]

ORA-01652 에러는 다음과 같은 상황에서 발생할 수 있습니다.

• 임시 테이블스페이스(temp tablespace)의 크기가 작아서 임시 세그먼트를 할당할 수 있는 공간이 부족한 경우
• 임시 테이블스페이스의 데이터파일(datafile)이 자동 확장(autoextend) 기능이 꺼져 있거나, 최대 크기(maxsize)에 도달한 경우
• 임시 테이블스페이스의 데이터파일이 저장된 스토리지 미디어의 용량이 부족한 경우

여러 세션(session)이 동시에 임시 테이블스페이스를 사용하면서 공간 경합(space contention)이 발생한 경우


[해결 방안]

ORA-01652 에러를 해결하기 위해서는 다음과 같은 절차를 따라야 합니다.

1. 임시 테이블스페이스의 크기와 사용량을 확인합니다.

--임시 테이블스페이스의 데이터파일의 크기와 상태를 확인할 수 있습니다.
select * from dba_temp_files where tablespace_name='temp';

--명령어로 임시 세그먼트의 사용량과 세션 정보를 확인할 수 있습니다.
select * from v$tempseg_usage;

2. 임시 테이블스페이스의 크기를 증가시킵니다. 

--명령어로 데이터파일의 크기를 조정할 수 있습니다.
alter database tempfile '***.dbf' resize ***M;
 
--명령어로 데이터파일의 자동 확장 기능을 켤 수 있습니다. 
alter database tempfile '***.dbf' autoextend on;
 
--명령어로 새로운 데이터파일을 추가할 수 있습니다.
alter tablespace temp add tempfile '***.dbf' size ***M;

3. 스토리지 미디어의 용량을 확인하고 증가시킵니다. 

`df -h` 명령어로 스토리지 미디어의 용량과 사용량을 확인할 수 있습니다. 

만약 스토리지 미디어의 용량이 부족하다면, 추가적인 스토리지를 할당하거나, 불필요한 파일을 삭제하거나, 다른 스토리지로 데이터파일을 이동시킬 수 있습니다.

4. 임시 테이블스페이스의 공간 경합을 해소합니다. 

--명령어로 임시 테이블스페이스의 공간 경합 상황을 확인할 수 있습니다.
select * from v$sort_segment where tablespace_name='temp';

만약 공간 경합이 심각하다면, 다음과 같은 방법으로 해소할 수 있습니다.

• 임시 테이블스페이스를 사용하는 세션을 셧다운하거나, 취소합니다.
• 임시 테이블스페이스를 사용하는 SQL 문장을 최적화하거나, 분할합니다.
• 임시 테이블스페이스를 여러 개로 분산하고, 세션별로 할당합니다.

 

 

[오류 예시]

임시 테이블스페이스의 크기가 작아서 임시 세그먼트를 할당할 수 없는 경우입니다.

SQL> select * from emp order by sal desc;
select * from emp order by sal desc
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace temp

해결방법은 다음과 같습니다.

1. 임시 테이블스페이스의 크기와 사용량을 확인합니다.

SQL> select * from dba_temp_files where tablespace_name='temp';
 
FILE_NAME                       FILE_ID TABLESPACE_NAME  BYTES     BLOCKS STATUS   RELATIVE_FNO AUT MAXBYTES   MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------------------------ ------- --------------- --------- ------ ------- ------------ --- --------- --------- ------------ ---------- -----------
/u01/app/oracle/oradata/temp01.dbf       1 TEMP            104857600   12800 AVAILABLE            1 YES 34359721984   4194302         6400  104857600       12800

임시 테이블스페이스의 데이터파일은 하나이고, 크기는 100MB입니다. 자동 확장 기능은 켜져 있고, 최대 크기는 32GB입니다.

SQL> select * from v$tempseg_usage;
 
TABLESPACE USERNAME     SESSION_ADDR    SESSION_NUM SQLADDR    SQLHASH BLOCKS EXTENTS SEGTYPE   CONTENTS
---------- ---------- ---------------- ----------- ---------- -------- ------ ------- -------- ----------
TEMP       SCOTT       00000000C3F8F9E8         123 0000000000        0      0       0 SORT      TEMPORARY
 

임시 세그먼트를 사용하는 세션은 하나이고, SCOTT 사용자입니다. 정렬 작업을 수행하고 있습니다.

2. 임시 테이블스페이스의 크기를 증가시킵니다.

SQL> alter database tempfile '/u01/app/oracle/oradata/temp01.dbf' resize 200M;
 
Database altered.

임시 테이블스페이스의 데이터파일의 크기를 200MB로 조정합니다.

3. 다시 SQL 문장을 실행합니다.

SQL> select * from emp order by sal desc;
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7788 SCOTT      ANALYST          7566 19-APR-87       3000                    20
      ...

 

 

[결론]

ORA-01652 오류는 Oracle 데이터베이스 환경에서 테이블스페이스의 공간 부족으로 인해 발생하는 오류로, 데이터의 크기와 성장률을 예측하여 적절한 용량을 유지하는 데이터베이스 용량 관리의 중요성을 강조합니다. 

오류를 해결하기 위해서는 데이터의 성장률을 고려하여 테이블스페이스를 확장하거나 데이터를 정리하고, 불균형한 인덱스를 최적화하는 등의 조치가 필요합니다.

 

 

#Oracle  #오라클  #오라클 오류코드  #ORA-01652  #오류해결  #DB용량관리  #DB저장소