[oracle] Temporary UNDO - 이해하기 쉽게

Oracle Linux Server release 8.1
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0

 

기본적으로 데이터베이스는 UNDO 정보를 UNDO Tablespace에 저장한다. 그렇다면 Temporary TABLE에 대해 DML이 발생하면 UNDO는 어디에 저장되는지 알아보자.

 

1. TEMP_UNDO_ENABLED 파라미터

파라미터 값이 FALSE이면 Temporary TABLE의 UNDO는 일반 테이블처럼 Undo Tablespace에 저장된다. 하지만 파라미터 값이 TRUE이면 Temporary TABLE의 UNDO는 Temporary Tablespace에 저장된다.

-- 파라미터 설정
    -- Session level
    ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;
    ALTER SESSION SET TEMP_UNDO_ENABLED = FALSE;

    -- System level
    ALTER SYSTEM SET TEMP_UNDO_ENABLED = TRUE;
    ALTER SYSTEM SET TEMP_UNDO_ENABLED = FALSE;

 

2. TEMPORARY 테이블 / TEMP_UNDO_ENABLED (FALSE)

-- 유저 접속
    CONN test01/rnd5815

-- TEMP_UNDO_ENABLED
    SHOW PARAMETER TEMP_UNDO_ENABLED (기본 FALSE)

-- 테이블 생성
    DROP TABLE my_temp_table PURGE;
    CREATE GLOBAL TEMPORARY TABLE my_temp_table (
      id           NUMBER,
      description  VARCHAR2(20)
    ) ON COMMIT DELETE ROWS;

-- 데이터 삽입
    INSERT INTO my_temp_table
    WITH data AS (
      SELECT 1 AS id
      FROM   dual
      CONNECT BY level < 10000
    )
    SELECT rownum, TO_CHAR(rownum)
    FROM   data a, data b
    WHERE  rownum <= 1000000;

-- UNDO 사용량 확인
    SET LINESIZE 200
    ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

    SELECT BEGIN_TIME, END_TIME, UNDOBLKS
    FROM   v$undostat
    WHERE  end_time >= SYSDATE - INTERVAL '1' MINUTE;

    BEGIN_TIME           END_TIME               UNDOBLKS
    -------------------- -------------------- ----------
    02-AUG-2024 11:22:20 02-AUG-2024 11:23:54        341

 

3. TEMPORARY 테이블 / TEMP_UNDO_ENABLED (TRUE)

-- 유저 접속
    CONN test01/rnd5815

-- TEMP_UNDO_ENABLED
    ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;
    SHOW PARAMETER TEMP_UNDO_ENABLED

-- 데이터 삽입
    INSERT INTO my_temp_table
    WITH data AS (
      SELECT 1 AS id
      FROM   dual
      CONNECT BY level < 10000
    )
    SELECT rownum, TO_CHAR(rownum)
    FROM   data a, data b
    WHERE  rownum <= 1000000;

-- Temporary UNDO 사용량 확인
    SET LINESIZE 200
    ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

    SELECT BEGIN_TIME, END_TIME, UNDOBLKCNT
    FROM   v$tempundostat
    WHERE  end_time >= SYSDATE - INTERVAL '1' MINUTE;

    BEGIN_TIME           END_TIME             UNDOBLKCNT
    -------------------- -------------------- ----------
    02-AUG-2024 11:22:20 02-AUG-2024 11:26:09        321

 

4. Reference

https://oracle-base.com/articles/12c/temporary-undo-12cr1
https://docs.oracle.com/cd/B19306_01/server.102/b14231/undo.htm