Undo tablespace 용량 확인 방법 및 조치

오라클 데이터베이스에서 UNDO 테이블스페이스의 용량을 확인하고 관리하는 방법은 여러 가지가 있습니다. 아래에서는 UNDO 테이블스페이스 용량을 확인하는 방법과 용량 부족 시에 취할 수 있는 조치 방법에 대해 설명합니다.

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

1.1 DBA_DATA_FILES 뷰를 사용하여 UNDO 테이블스페이스 크기 확인

DBA_DATA_FILES 뷰를 사용하면 UNDO 테이블스페이스 파일의 크기 정보를 확인할 수 있습니다.

SELECT tablespace_name, file_name, bytes / 1024 / 1024 AS size_mb, autoextensible, maxbytes / 1024 / 1024 AS max_size_mb
FROM dba_data_files
WHERE tablespace_name = 'UNDOTBS1'; -- 'UNDOTBS1'는 실제 UNDO 테이블스페이스 이름

• tablespace_name: 테이블스페이스 이름
• file_name: 데이터 파일의 이름과 경로
• size_mb: 현재 테이블스페이스 크기(MB)
• autoextensible: 자동 확장 여부
• max_size_mb: 자동 확장이 가능할 경우 최대 크기(MB)

1.2 DBA_FREE_SPACE 뷰를 사용하여 UNDO 테이블스페이스의 남은 용량 확인

DBA_FREE_SPACE 뷰를 사용하여 UNDO 테이블스페이스의 남은 공간을 확인할 수 있습니다.

SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS free_space_mb
FROM dba_free_space
WHERE tablespace_name = 'UNDOTBS1'
GROUP BY tablespace_name;

이 쿼리는 UNDO 테이블스페이스의 남은 자유 공간을 MB 단위로 보여줍니다.

1.3 V$UNDOSTAT 뷰를 사용하여 UNDO 사용량 추세 확인

V$UNDOSTAT 뷰를 사용하여 UNDO 테이블스페이스의 사용량 추세와 트랜잭션 관련 정보를 확인할 수 있습니다

SELECT begin_time, end_time, undoblks, maxquerylen, txncount
FROM v$undostat
ORDER BY begin_time DESC;

• undoblks: UNDO 블록 수
• maxquerylen: 가장 긴 쿼리 시간(초)
• txncount: 해당 시간 동안의 트랜잭션 수

이 정보는 UNDO 사용 추세를 분석하는 데 도움이 됩니다.

2. UNDO 테이블스페이스 용량 부족 시 조치 방법

2.1 UNDO 테이블스페이스 크기 확장

UNDO 테이블스페이스의 크기가 부족하면 데이터 파일을 확장하여 용량을 늘릴 수 있습니다.

2.1.1 자동 확장 설정 (AUTOEXTEND)

UNDO 테이블스페이스의 데이터 파일에 AUTOEXTEND 옵션을 설정하여 필요할 때 자동으로 크기가 늘어나도록 설정할 수 있습니다.

ALTER DATABASE DATAFILE '/path_to_undo_tablespace/undotbs01.dbf'
AUTOEXTEND ON
NEXT 50M MAXSIZE UNLIMITED;

• NEXT 50M: 데이터 파일이 확장될 때 한 번에 50MB씩 증가
• MAXSIZE UNLIMITED: 최대 크기를 무제한으로 설정 (제한을 두고 싶다면 MAXSIZE를 MB 또는 GB 단위로 설정)

2.1.2 수동으로 UNDO 테이블스페이스 크기 확장

자동 확장이 아닌 수동으로 데이터 파일 크기를 늘리려면 다음 명령어를 사용할 수 있습니다.

ALTER DATABASE DATAFILE '/path_to_undo_tablespace/undotbs01.dbf' RESIZE 2G;

 

2.2 UNDO_RETENTION 설정 조정

UNDO 용량 부족은 UNDO 테이블스페이스에 보관되는 데이터의 보존 시간(UNDO_RETENTION)과 관련이 있을 수 있습니다. 보존 시간을 줄이면 UNDO 테이블스페이스의 사용량을 줄일 수 있습니다.

ALTER SYSTEM SET UNDO_RETENTION = 900;

• UNDO_RETENTION 값을 초 단위로 설정하며, 위의 예시는 900초(15분)를 의미합니다.
• 너무 짧게 설정하면 트랜잭션 롤백이 필요할 때 필요한 UNDO 데이터를 사용할 수 없을 수 있으므로, 적절한 값으로 설정해야 합니다.