[Oracle] UNDO 테이블스페이스 조회, 생성, 삭제, 변경

UNDO 테이블스페이스

Oracle 데이터베이스에서 트랜잭션 관리와 데이터 복구에 중요한 역할을 하는 특별한 테이블스페이스

 

UNDO 테이블스페이스의 주요 역할

1. 트랜잭션 롤백 지원

사용자가 데이터를 수정하거나 삭제하는 중 오류가 발생하거나 명시적으로 ROLLBACK 명령을 실행하면, UNDO 테이블스페이스를 사용하여 이전 데이터 상태로 복원

 

2. 데이터 읽기 일관성(Read Consistency) 보장

다른 사용자가 데이터를 수정하는 중에도, UNDO 데이터를 사용하여 트랜잭션 시작 시점의 데이터를 읽을 수 있도록 보장

 

3. 인스턴스 복구

데이터베이스가 비정상 종료되었을 때, UNDO 테이블스페이스를 사용하여 비정상 트랜잭션을 롤백하거나 커밋되지 않은 데이터를 복구

 

4. 플래시백 기능 지원

UNDO 테이블스페이스는 플래시백 쿼리(Flashback Query)와 같은 기능을 지원하며, 이를 통해 특정 시점의 데이터 상태를 조회하거나 복구

 

 

---

 

테이블스페이스 용량 조회 쿼리

SELECT A.TABLESPACE_NAME 
     , ROUND(MAXBYTES / 1048576, 2) "최대용량(MB)"
     , ROUND(A.BYTES_ALLOC / 1024 / 1024, 2) "할당용량(MB)"
     , ROUND(NVL(B.BYTES_FREE, 0) / 1024 / 1024, 2) "여유용량(MB)"
     , ROUND((A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0)) / 1024 / 1024, 2) "사용량(MB)"
     , ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100, 2) "여유%"
     , 100 - ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100, 2) "사용%"
  FROM (
        SELECT F.TABLESPACE_NAME, SUM(F.BYTES) BYTES_ALLOC
             , SUM(DECODE(F.AUTOEXTENSIBLE, 'YES', F.MAXBYTES, 'NO', F.BYTES)) MAXBYTES
          FROM DBA_DATA_FILES F
         GROUP BY TABLESPACE_NAME
       ) A
     , (
        SELECT F.TABLESPACE_NAME, SUM(F.BYTES) BYTES_FREE
          FROM DBA_FREE_SPACE F
         GROUP BY TABLESPACE_NAME
       ) B
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME (+)
 ORDER BY 1
;

 

 

UNDO 테이블스페이스 조회

• VALUE값에 테이블스페이스명 확인 (기본적으로는 UNDOTBS1)

SELECT *
  FROM V$PARAMETER
 WHERE UPPER(NAME) = 'UNDO_TABLESPACE'
;

SELECT TABLESPACE_NAME, STATUS, CONTENTS
  FROM DBA_TABLESPACES
 WHERE CONTENTS = 'UNDO'
;

 

 

UNDO 데이터파일 조회

SELECT FILE_NAME, TABLESPACE_NAME, STATUS
  FROM DBA_DATA_FILES
 WHERE TABLESPACE_NAME IN (SELECT TABLESPACE_NAME
                             FROM DBA_TABLESPACES
                            WHERE CONTENT = 'UNDO')
;

 

 

새로운 UNDO 테이블스페이스 생성

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'C:\app\HYUN\product\21c\oradata\XE\UNDOTBS02.DBF' 
                                SIZE 500M         -- 데이터파일 초기 크기 설정
                                AUTOEXTEND ON     -- 자동 확장 설정
                                NEXT 8K           -- 확장될 때, 8K씩 증가하도록 설정
                                MAXSIZE UNLIMITED -- 데이터파일의 최대 크기를 제한하지 않음
;

 

 

새 UNDO 테이블스페이스로 변경

ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2;

 

 

기존 테이블스페이스 OFFLINE으로 변경

ALTER TABLESPACE UNDOTBS1 OFFLINE;

 

 

만약 [ORA-30042: Cannot offline the undo tablespace] 라는 오류가 뜬다면 DB 재기동 후 다시 OFFLINE으로 변경

Microsoft Windows [Version 10.0.26100.2454]
(c) Microsoft Corporation. All rights reserved.

C:\Users\HYUN>sqlplus "/as sysdba"

SQL*Plus: Release 21.0.0.0.0 - Production on 화 1월 7 16:08:26 2025
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


다음에 접속됨:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> SHUTDOWN IMMEDIATE;
데이터베이스가 닫혔습니다.
데이터베이스가 마운트 해제되었습니다.
ORACLE 인스턴스가 종료되었습니다.
SQL> STARTUP;
ORACLE 인스턴스가 시작되었습니다.

Total System Global Area 1610610072 bytes
Fixed Size                  9856408 bytes
Variable Size            1006632960 bytes
Database Buffers          587202560 bytes
Redo Buffers                6918144 bytes
데이터베이스가 마운트되었습니다.
데이터베이스가 열렸습니다.
SQL>

 

 

(위 오류로 인해 DB 재기동을 하였으면) 기존 테이블스페이스 OFFLINE으로 변경

ALTER TABLESPACE UNDOTBS1 OFFLINE;

 

 

기존 UNDO 테이블스페이스 삭제

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

 

 

 

---

 

 

기존 (UNDO) 테이블스페이스에 새로운 데이터파일 추가하는 경우

• (UNDO) 테이블스페이스명 입력하는 부분에 다른 테이블스페이스명 입력하여도 데이터파일 추가 가능한 쿼리

ALTER TABLESPACE UNDOTBS1 ADD DATAFILE 'C:\app\HYUN\product\21c\oradata\XE\UNDOTBS02.DBF'
                              SIZE 500M         -- 데이터파일 초기 크기 설정
                              AUTOEXTEND ON     -- 자동 확장 설정
                              NEXT 8K           -- 확장될 때, 8K씩 증가하도록 설정
                              MAXSIZE UNLIMITED -- 데이터파일의 최대 크기를 제한하지 않음
;