Oracle UNDO 테이블스페이스 용량 확인 및 부족 오류 해결 방법 총정리

Oracle DB를 운영하다 보면 종종 UNDO 테이블스페이스 용량 부족으로 인한 에러가 발생할 수 있습니다. 특히 ORA-01555: snapshot too old와 같은 오류는 많은 DBA들을 괴롭히는 대표적인 문제입니다.

이 글에서는 Oracle UNDO 테이블스페이스의 용량을 확인하는 방법부터, 부족할 때의 조치 방안까지 단계별로 정리하였습니다. 실무에서 바로 적용 가능한 SQL 쿼리와 설정 방법도 함께 소개하니 꼭 끝까지 읽어보세요.

🔍 1. UNDO 테이블스페이스 용량 확인 방법

 

-- 데이터파일 위치 및 크기 확인
SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb, autoextensible
FROM dba_data_files
WHERE tablespace_name LIKE 'UNDO%';

 

-- 사용 중인 UNDO 용량 확인
SELECT a.tablespace_name,
       ROUND(a.bytes/1024/1024, 2) AS total_mb,
       ROUND(b.bytes/1024/1024, 2) AS used_mb,
       ROUND((b.bytes/a.bytes)*100, 2) AS used_pct
FROM
  (SELECT tablespace_name, SUM(bytes) AS bytes
   FROM dba_data_files
   GROUP BY tablespace_name) a,
  (SELECT tablespace_name, SUM(bytes) AS bytes
   FROM dba_undo_extents
   WHERE status IN ('ACTIVE', 'UNEXPIRED')
   GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
  AND a.tablespace_name LIKE 'UNDO%';

 

⚠️ 2. UNDO 공간 부족 시 발생하는 오류

• ORA-01555: snapshot too old
• DML 작업 중 rollback 불가
• 트랜잭션 중단 및 성능 저하

---

🛠️ 3. UNDO 테이블스페이스 부족 시 조치 방법

✅ 자동 확장 설정

ALTER DATABASE DATAFILE '/경로/undo01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

 

✅ 수동 확장

ALTER DATABASE DATAFILE '/경로/undo01.dbf' RESIZE 2G;

 

✅ 데이터파일 추가

ALTER TABLESPACE UNDO_TBS_NAME ADD DATAFILE '/경로/undo02.dbf' SIZE 1G AUTOEXTEND ON;

 

✅ UNDO 보존 기간(UNDO_RETENTION) 조정

-- 현재 설정 확인
SHOW PARAMETER undo_retention;

-- 설정 변경
ALTER SYSTEM SET UNDO_RETENTION = 900; -- 단위: 초

 

✅ 장시간 트랜잭션 모니터링

SELECT s.sid, s.serial#, t.used_urec, t.used_ublk
FROM v$transaction t, v$session s
WHERE s.saddr = t.ses_addr;

 

✅ 마무리 정리

Oracle UNDO 테이블스페이스는 작은 문제 같아 보여도, 시스템 안정성과 직결된 중요한 요소입니다. 오늘 소개한 확인 및 조치 방법을 숙지하고, 주기적으로 UNDO 상태를 점검해보세요. 문제가 발생하기 전에 미리 예방하는 것이 가장 좋은 대응입니다.