[Oracle] transaction full 오류

Oracle transaction full 오류

0. 개요

Oracle 데이터베이스에서 transaction full 오류는 주로 UNDO 공간 부족, ITL 슬롯 부족, TEMP/테이블스페이스 확장 불가 등으로 인해 새로운 트랜잭션을 정상적으로 처리하지 못할 때 발생한다. 이런 상황에서는 DML 작업이 실패하거나 성능이 급격히 저하되고, 심한 경우 서비스 전체가 멈춰버릴 수 있다.

대표적으로 연관될 수 있는 오류 코드 예시:
- ORA-30036: unable to extend segment in undo tablespace by ...
- ORA-1652 : unable to extend temp segment by ... in tablespace ...
- ORA-01653: unable to extend table ... in tablespace ...
- ORA-01555: snapshot too old – rollback segment too small
- ITL/transaction slot 부족으로 인한 “maximum number of concurrent transactions” 관련 오류

1️⃣ 증상

실제 운영 환경에서는 아래와 같은 형태로 transaction full 관련 오류가 나타나는 경우가 많다.

• 대량 INSERT·UPDATE·DELETE 수행 중 갑자기 ORA-30036 또는 ORA-1652 발생
• 동일 테이블에 동시 접속 세션이 많을 때 특정 세션만 반복적으로 에러 발생
• 대량 배치 작업이 특정 시점 이후부터 실패하면서 롤백 수행
• 긴 트랜잭션(장시간 미커밋 세션)이 많은 경우 스냅샷 관련 오류 동반

ORA-30036: unable to extend segment by 128 in undo tablespace UNDOTBS1
ORA-1652: unable to extend temp segment by 64 in tablespace TEMP
ORA-01555: snapshot too old: rollback segment number 7 with name "_SYSSMU7$" too small
    

2️⃣ 원인

■ UNDO/롤백 세그먼트 공간 부족

• UNDO 테이블스페이스 크기 부족 또는 자동 확장 비활성화
• 장시간 커밋되지 않는 대용량 트랜잭션 증가
• 대량 배치 수행 시간과 OLTP 피크 타임이 겹치는 경우

■ TEMP/데이터 테이블스페이스 공간 부족

• 대규모 정렬/조인/해시 연산으로 TEMP 사용량 급증
• 테이블/인덱스 확장 필요량 대비 데이터파일 크기 한계 도달

■ ITL/transaction slot 부족

• 테이블/인덱스 블록당 ITL 슬롯 수가 너무 작게 설정된 경우
• 동일 블록에 동시에 DML을 수행하는 세션이 매우 많은 경우

정리하면,
- 공간(UNDO/TEMP/데이터) 부족
- 트랜잭션이 과도하게 길거나 동시성이 과도함
- 블록 구조 및 저장공간 설정이 실제 workload에 맞지 않음
이 세 가지가 transaction full 오류의 핵심 원인이다.

3️⃣ 분석

transaction full 오류를 정확히 분석하려면 단순히 에러 메시지만 보는 것이 아니라 아래 항목들을 함께 확인해야 한다.

■ 1) UNDO 사용량 및 설정 확인

-- UNDO 테이블스페이스 및 사용량 확인
SELECT tablespace_name, status, contents
     , extent_management, SEGMENT_SPACE_MANAGEMENT
FROM   dba_tablespaces
WHERE  contents = 'UNDO';

SELECT tablespace_name, sum(bytes)/1024/1024 AS mb_used
FROM   dba_undospace_usage
GROUP  BY tablespace_name;
    

■ 2) TEMP 사용량 및 정렬 작업 확인

SELECT tablespace_name, SUM(blocks*8)/1024 AS mb_used
FROM   v$tempseg_usage
GROUP  BY tablespace_name;

SELECT username, sql_id, blocks
FROM   v$tempseg_usage
ORDER  BY blocks DESC;
    

■ 3) 장시간 트랜잭션 및 ITL 경합 세션 확인

-- 장시간 미커밋 세션
SELECT s.sid, s.serial#, s.username, t.used_ublk, t.used_urec
FROM   v$transaction t, v$session s
WHERE  t.ses_addr = s.saddr
ORDER BY t.used_ublk DESC;

-- ITL 경합 여부(예시: AWR / ASH 또는 블록 트레이스 필요)
    

이 정보들을 기반으로, 어떤 테이블스페이스가 어떤 SQL/세션 때문에 가득 찌는지 파악하는 것이 transaction full 오류 분석의 출발점이다.

4️⃣ 조치

■ 1) UNDO/TEMP/데이터 테이블스페이스 용량 확장

• 자동 확장(autoextend) 설정 및 최대 크기 상향
• 새로운 데이터파일 추가로 테이블스페이스 크기 확장
• 가능하다면 별도 디스크 그룹에 UNDO/TEMP 분리 배치

ALTER DATABASE DATAFILE '/u01/oradata/UNDOTBS01.dbf'
  AUTOEXTEND ON NEXT 512M MAXSIZE 50G;

ALTER TABLESPACE TEMP
  ADD TEMPFILE '/u01/oradata/TEMP02.dbf'
  SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 50G;
    

■ 2) 장시간 트랜잭션 정리 및 배치 스케줄 조정

• 사용자/배치 프로그램에서 불필요하게 긴 트랜잭션 분리
• 대량 작업은 야간/비피크 시간대로 이동
• 정기적으로 미커밋 세션 모니터링 및 장애 조치 프로세스 수립

■ 3) ITL/블록 구조 조정

• 동시 DML이 많은 테이블에 대해 INITRANS·MAXTRANS 재설정
• PCTFREE 조정으로 블록 내 여유 공간 확보
• 필요 시 재구성(online redefinition)으로 테이블 재배치

ALTER TABLE hr.large_tx_table
  INITRANS 10
  PCTFREE 20;
    

■ 4) 모니터링 및 알람 체계 강화

• UNDO/TEMP 사용률 임계치(예: 70%, 90%) 알람 설정
• 장시간 트랜잭션 감지 스크립트/모니터링 대시보드 구성
• 배치·대량 작업 전용 계정/리소스 그룹 정의

5️⃣ 사후 관리 및 로그 정리

transaction full 오류가 한 번 발생했다면, 동일한 패턴이 반복될 가능성이 높다. 따라서 단순히 공간을 늘리는 것에서 끝내지 말고 아래 항목을 정리해야 한다.

• 발생 시각, 관련 세션, 실행 SQL 로그 정리
• 어떤 테이블스페이스가 어느 정도까지 차올랐는지 기록
• 해당 트랜잭션이 비즈니스적으로 꼭 필요한 방식인지 재검토
• 향후 재발 시 자동으로 세션/SQL을 식별할 수 있는 조회 스크립트 마련

6️⃣ 예방 전략

transaction full 오류를 근본적으로 예방하려면 용량 확장뿐 아니라 트랜잭션 설계·배치 전략·모니터링 체계를 함께 개선해야 한다.

예방 체크리스트:
- UNDO/TEMP/데이터 테이블스페이스 용량·자동 확장 정책 정의
- 대량 작업 전용 시간대 및 전용 계정 운영
- 미커밋 세션 장기 방치 금지 정책 수립
- ITL/블록 구조를 workload에 맞게 튜닝
- 정기적인 AWR/ASH 분석으로 상위 자원 사용 SQL 파악

7️⃣ 기본 오류 대응 4단계

1단계 원인 파악
2단계 영향 범위 확인
3단계 임시 조치 적용 (세션 종료, 공간 임시 확장 등)
4단계 근본 원인 제거 (용량 정책·트랜잭션 설계 개선)

오류 분석 흐름도

• 외부 노출 여부 확인 (서비스 영향·고객 영향 범위 파악)
• 관련 역할·기능 비활성화 (문제 배치 또는 기능 일시 중지)
• 관련 포트·접속 경로 차단 (불필요한 대량 작업·툴 접속 제한)
• 로그 모니터링 및 재발 여부 상시 관제