728x90
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 -- 데이터파일의 최대 크기를 제한하지 않음
;
728x90
반응형
'DB' 카테고리의 다른 글
| [Oracle] FlashBack 데이터 복구 방법, 복원 쿼리 (2) | 2024.12.06 |
|---|---|
| [Oracle] sql 파일, 스크립트 자동 실행 (sqlplus) (0) | 2024.10.04 |
| [Oracle] Oracle Database 21c 윈도우 설치 및 계정 생성, 권한 부여 (0) | 2024.10.04 |