Redefinition을 이용한 CLOB TO BLOB 컬럼 변환

ReDefinition

Redefinition을 이용한 CLOB TO BLOB 컬럼 변환

돼지와함께춤을 2025. 5. 21. 16:30

본 글은 테이블에 CLOB 컬럼을 BLOB 타입으로 변환하는 과정을 다뤄보겠다.

C : Character

B : Binary

LOB는 Large OBject 둘다 대용량 값을 저장할때 적합한 타입으로

CLOB는 보통 XML에 있는 쿼리문, 아주 긴 텍스트문 등이 자주 쓰이며

BLOB는 파일을 저장할때 쓰인다 (하지만 보통 파일은 파일서버 DB엔 경로만 저장)

바이너리 타입이 사이즈가 덜 잡힐것 같아서 문자열인 CLOB컬럼을 BLOB로 바꿔보았다.

결론부터 말하면 거기서 거기~ COMPRESS가 아닌 이상 드라마틱한 효과는 없는 것 같고 괜히 저장, 조회시 캐스팅 해야한다.

varchar2 < ㅡ > number 처럼 alter문으로 타입변경은 지원되지 않고

새로운 컬럼 add후 데이터를 캐스팅해서 마이그레이션 하거나 redefinition으로 새로운 테이블을 스위칭해야한다.

운영환경에 쓸라면 redefinition해보자

간단하게 미리 순서 나열하면

용량검토
temp테이블 생성
clob2blob 사용자정의 함수 생성
실시간 dml 스크립트 작성하여 데이터 손실 테스트
redef
검증

주의할점은 redef작업은 Materialized View를 생성하여 데이터 변경 및 추적에 사용한다

MV에서 함수 호출시 해당 함수가 같은 입력값에 대해 항상 같은 결과를 반환을 명시하는 Deterministic이 선언되지 않으면

오류가 발생한다.

**Materialized View (MV)**는 질의 결과를 물리적으로 저장해 두는 구조입니다. 즉, MV를 만든 시점의 함수 결과값을 저장하고, 그 후에는 **리프레시(refresh)**가 일어나기 전까지는 다시 계산하지 않습니다.

위는 갓 GPT 왈 .. 정리하면 MV는 데이터 일관성을 위해 Deterministic 함수만 호출할 수 있다.

LOB사양 확인

-- ===================== LOB사양 확인 =====================
SELECT OWNER,TABLE_NAME,COLUMN_NAME,CACHE,LOGGING,ENCRYPT,COMPRESSION,DEDUPLICATION,SEGMENT_NAME,SECUREFILE
FROM DBA_LOBS 
WHERE OWNER = '[스키마명]'
  AND TABLE_NAME = '[테이블명]';
-- [스키마명]	[테이블명]	  [LOB컬럼1]	NO	YES	NO	NO	NO	SYS_LOB0000271029C00004$$	YES
-- [스키마명]	[테이블명]	  [LOB컬럼2]	NO	YES	NO	NO	NO	SYS_LOB0000271029C00005$$	YES
-- [스키마명]	[테이블명]	  [LOB컬럼3]	NO	YES	NO	NO	NO	SYS_LOB0000271029C00006$$	YES
-- [스키마명]	[테이블명]	  [LOB컬럼4]	NO	YES	NO	NO	NO	SYS_LOB0000271029C00007$$	YES

LOB컬럼별 세그먼트 용량

-- ===================== LOB컬럼별 세그먼트 용량=====================
SELECT BYTES /1024 /1024 /1024, A.*
FROM DBA_SEGMENTS A
WHERE TABLESPACE_NAME = '[해당 테이블이 속한 테이블스페이스명]'
AND SEGMENT_NAME IN ('SYS_LOB0000271029C00004$$', 'SYS_LOB0000271029C00005$$','SYS_LOB0000271029C00006$$', 'SYS_LOB0000271029C00007$$')
ORDER BY 1 desc;
--4.75018310546875	[스키마명]	  SYS_LOB0000271029C00004$$
--0.00506591796875	[스키마명]	  SYS_LOB0000271029C00005$$
--0.00311279296875	[스키마명]	  SYS_LOB0000271029C00006$$
--0.0001220703125	[스키마명]	  SYS_LOB0000271029C00007$$

LOB 제외 테이블 세그먼트 용량

-- ===================== LOB 제외 테이블 전체용량 체크 =====================
SELECT BYTES /1024 /1024 /1024, A.*
FROM DBA_SEGMENTS A
WHERE OWNER = '[스키마명]'
  AND SEGMENT_NAME = '[테이블명]';
--0.6171875	[스키마명]      [테이블명]

데이터파일 추가 검토

-- ===================== 체크 후 데이터파일 추가 검토 위 (LOB세그먼트 + LOB제외 테이블세그먼트)용량의 2배이상은 확보해야함 =====================
SELECT SUM(BYTES) /1024 /1024 /1024, SUM(MAXBYTES) /1024 /1024 /1024 FROM DBA_DATA_FILES 
WHERE TABLESPACE_NAME = '[테이블스페이스명]';
--34.619140625	60   // 26GB ~ 최대 53GB 사용가능해보임

REDEF 체크

-- ===================== REDEF 가능 여부 확인 =====================
BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE(
    uname => '[스키마명]',
    tname => '[테이블명]',
    options_flag => DBMS_REDEFINITION.CONS_USE_PK  -- 또는 CONS_USE_ROWID
  );
END;
/

INTERIM 테이블 생성

-- ===================== INT_TABLE 생성 // 제약조건(NOT NULL 포함), 트리거, 인덱스, 권한 등 종속객체 제외하고 생성=====================
CREATE TABLE 테이블_TEMP_CLOB2BLOB
(
    CD              VARCHAR2(3)  ,
    ID              VARCHAR2(10) ,
    NO              NUMBER(10)   ,
    LOB컬럼1         BLOB         ,
    LOB컬럼2         BLOB         ,
    LOB컬럼3         BLOB         ,
    LOB컬럼4         BLOB         ,
    FST_RGSTR_ID    VARCHAR2(10) ,
    FST_RGST_TS     TIMESTAMP(6) DEFAULT SYSTIMESTAMP  ,
    LAST_UPDTR_ID   VARCHAR2(10) ,
    LAST_UPDT_TS    TIMESTAMP(6) DEFAULT SYSTIMESTAMP  
)
TABLESPACE [테이블스페이스명]
STORAGE
(
    INITIAL 64K
    NEXT 1M
)
NOCOMPRESS 
LOB (LOB컬럼1) STORE AS SECUREFILE 
(
    STORAGE
    (
        INITIAL 104K
        NEXT 1M
    )
    CHUNK 8192
    PCTVERSION 0
)
LOB (LOB컬럼2) STORE AS SECUREFILE 
(
    STORAGE
    (
        INITIAL 104K
        NEXT 1M
    )
    CHUNK 8192
    PCTVERSION 0
)
LOB (LOB컬럼3) STORE AS SECUREFILE 
(
    STORAGE
    (
        INITIAL 104K
        NEXT 1M
    )
    CHUNK 8192
    PCTVERSION 0
)
LOB (LOB컬럼4) STORE AS SECUREFILE 
(
    STORAGE
    (
        INITIAL 104K
        NEXT 1M
    )
    CHUNK 8192
    PCTVERSION 0
);

사용자정의 함수 생성

-- ===================== CLOB -> BLOB 사용자 함수생성 REDEF시 MV를 생성하는데 MV에서 호출하는 사용자함수는 DETERMINISTINC 속성이 기술되어야함 =====================
CREATE OR REPLACE FUNCTION [스키마].clob2blob (p_clob CLOB) 
RETURN BLOB DETERMINISTIC IS
  l_tgt_idx  INT := 1;
  l_src_idx  INT := 1;
  l_blob     BLOB;
  l_lang     INT := dbms_lob.default_lang_ctx;
  l_err      INT := dbms_lob.warn_inconvertible_char;
BEGIN
  -- CLOB의 길이가 0보다 클 때만 실행 데이터에 0값이 있다면 함수 실행 오류
  IF dbms_lob.getlength(p_clob) > 0 THEN
    dbms_lob.createtemporary(
      lob_loc => l_blob,
      cache   => TRUE);
    dbms_lob.converttoblob(
      dest_lob    => l_blob,
      src_clob    => p_clob,
      amount      => dbms_lob.lobmaxsize,
      dest_offset => l_tgt_idx,
      src_offset  => l_src_idx,
      blob_csid   => dbms_lob.default_csid,
      lang_context=> l_lang,
      warning     => l_err);
  ELSE
    -- 길이가 0이면 NULL 반환
    RETURN NULL;
  END IF;

  RETURN l_blob;
END;
/

데이터 손실 테스트 스크립트 실행

-- ===================== 데이터 손실 테스트 다른 세션에서 실시간 DML INS =====================
DECLARE
    v_count NUMBER := 200;  -- 원하는 만큼 반복 (예: 10개)
BEGIN
    FOR i IN 1..v_count LOOP
        INSERT INTO [테이블명] (
            CD, ID, NO, LOB컬럼1, LOB컬럼2, LOB컬럼3, LOB컬럼4,
            FST_RGSTR_ID, FST_RGST_TS, LAST_UPDTR_ID, LAST_UPDT_TS
        ) VALUES (
            'LJG',                            
            'P'||TO_CHAR(SYSDATE, 'HH24MISS')||LPAD(i,3,'0'), 
            i,                              
            'LOB 1 : ' || i,             -- TMLN
            'LOB 2 : ' || i,             -- GRUP
            'LOB 2 : ' || i,             -- ZMBR
            'LOB 2 : ' || i,             -- INFO
            'LJG',                         -- FST_RGSTR_ID
            SYSTIMESTAMP,                    -- FST_RGST_TS
            'LJG',                         -- LAST_UPDTR_ID
            SYSTIMESTAMP                     -- LAST_UPDT_TS
        );

        COMMIT;

        DBMS_LOCK.SLEEP(1);  -- 1초 대기
    END LOOP;
END;
/

~~REDEF 취소~~

-- ===================== REDEF 취소(참고용 필요시 사용) =====================
BEGIN
  DBMS_REDEFINITION.ABORT_REDEF_TABLE(
    uname        => '[스키마명]',
    orig_table   => '[테이블명]',
    int_table    => '[INTERIM테이블명]'
  );
END;
/

REDEF START

-- ===================== REDEF =====================
BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname        => '[스키마명]',
    orig_table   => '[테이블명]',
    int_table    => '[INTERIM테이블명]',
    col_mapping => 'CD, 
                   ID, 
                   NO, 
                   CLOB2BLOB(LOB컬럼1) LOB컬럼1, 
                   CLOB2BLOB(LOB컬럼2) LOB컬럼2, 
                   CLOB2BLOB(LOB컬럼3) LOB컬럼3, 
                   CLOB2BLOB(LOB컬럼4) LOB컬럼4,
                   FST_RGSTR_ID,
                   FST_RGST_TS,
                   LAST_UPDTR_ID,
                   LAST_UPDT_TS',
    options_flag => DBMS_REDEFINITION.CONS_USE_PK
  );
END;
/

종속 객체 복사

-- ===================== 종속객체 복사 INDEX, TRIGGER, CONSTARINT, PRIV =====================
DECLARE
  l_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    uname            => '[스키마명]',
    orig_table       => '[테이블명]',
    int_table        => '[INTERIM테이블명]',
    copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
    copy_triggers    => TRUE,
    copy_constraints => TRUE,
    copy_privileges  => TRUE,
    ignore_errors    => TRUE,
    num_errors       => l_errors,
    copy_statistics  => TRUE,
    copy_mvlog       => FALSE
  );

  DBMS_OUTPUT.PUT_LINE('복사 중 오류 수: ' || l_errors);
END;
/

SYNC

-- ===================== SYNC 수행시간이 짧아질때까지 최대한 여러번 =====================
BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
    uname        => '[스키마명]',
    orig_table   => '[테이블명]',
    int_table    => '[INTERIM테이블명]'
  );
END;
/

FINISH

-- ===================== SYNC 후 빠르게 FINISH =====================
--실전 배포 전략
--1. 쿼리 수정 준비
--WAS 코드 내 INSERT INTO ... clob_column 등을
--INSERT INTO ... text_to_blob 으로 변경 / 문자열을 UTL_RAW.CAST_TO_RAW()로 감싸도록 수정 
--
--2. DBA와 타이밍 조율
--DBA가 SWAP 시점을 사전에 알려줍니다 (DBMS_REDEFINITION.FINISH_REDEF_TABLE)
--
--해당 시점 직후에 WAS 재배포 또는 DB 접근 코드만 hot swap
BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(
    uname        => '[스키마명]',
    orig_table   => '[테이블명]',
    int_table    => '[INTERIM테이블명]'
  );
END;
/
-- REDEF 작업 남아있는지 확인 
SELECT * FROM DBA_REDEFINITION_OBJECTS WHERE BASE_TABLE_NAME = '[테이블명]';

검증

-- ===================== 전체 ROW 확인 =====================
SELECT COUNT(*) FROM [INTERIM테이블명]
UNION ALL
SELECT COUNT(*) FROM [테이블명]

-- ===================== BLOB2CLOB함수 생성 =====================
CREATE OR REPLACE FUNCTION [스키마명].blob2clob(p_blob IN BLOB) RETURN CLOB IS
  v_clob   CLOB;
  v_raw    RAW(32767);
  v_len    PLS_INTEGER := DBMS_LOB.GETLENGTH(p_blob);
  v_pos    PLS_INTEGER := 1;
  v_chunk  PLS_INTEGER := 2000;
BEGIN
  DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
  WHILE v_pos <= v_len LOOP
    v_raw := DBMS_LOB.SUBSTR(p_blob, v_chunk, v_pos);
    DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(UTL_RAW.CAST_TO_VARCHAR2(v_raw)), UTL_RAW.CAST_TO_VARCHAR2(v_raw));
    v_pos := v_pos + v_chunk;
  END LOOP;
  RETURN v_clob;
END;
/

-- ===================== 데이터 손실 확인 =====================
SELECT 
  CD, ID, NO,
  [스키마명].BLOB2CLOB(LOB컬럼1) AS LOB컬럼1,
  [스키마명].BLOB2CLOB(LOB컬럼2) AS LOB컬럼2,
  [스키마명].BLOB2CLOB(LOB컬럼3) AS LOB컬럼3,
  [스키마명].BLOB2CLOB(LOB컬럼4) AS LOB컬럼4,
  LOB컬럼1,LOB컬럼2,LOB컬럼3,LOB컬럼4
FROM [스키마명].[테이블명] WHERE CD = 'LJG' ORDER BY NO;

-- ===================== BLOB상태로 변환되었는지도 확인 =====================
SELECT 
  CD, ID, NO,
  [스키마명].BLOB2CLOB(LOB컬럼1) AS LOB컬럼1,
  [스키마명].BLOB2CLOB(LOB컬럼2) AS LOB컬럼2,
  [스키마명].BLOB2CLOB(LOB컬럼3) AS LOB컬럼3,
  [스키마명].BLOB2CLOB(LOB컬럼4) AS LOB컬럼4,
  LOB컬럼1,LOB컬럼2,LOB컬럼3,LOB컬럼4
FROM [스키마명].[테이블명];



-- ===================== 통계 COPY 확인 =====================
SELECT * FROM dba_tab_statistics WHERE table_name = '[INTERIM테이블명]' AND owner = '[스키마명]'
UNION ALL 
SELECT * FROM dba_tab_statistics WHERE table_name = '[테이블명]' AND owner = '[스키마명]'
;

-- ===================== 종속 객체 확인 =====================
SELECT 'TRIGGER' AS OBJECT_TYPE,
       trigger_name AS OBJECT_NAME,
       status AS STATUS,
       NULL AS EXTRA
  FROM dba_triggers
 WHERE table_name = '[테이블명]' AND owner = '[스키마명]'
UNION ALL
SELECT 'INDEX',
       index_name,
       status,
       index_type || ' / ' || uniqueness
  FROM dba_indexes
 WHERE table_name = '[테이블명]' AND owner = '[스키마명]'
UNION ALL
SELECT 'CONSTRAINT',
       constraint_name,
       status,
       'CONSTRAINT_TYPE = ' || constraint_type || ' / Validated=' || validated || ' / CONDITION = ' || TO_CHAR(SEARCH_CONDITION_VC)
  FROM dba_constraints
 WHERE table_name = '[테이블명]' AND owner = '[스키마명]'
UNION ALL
SELECT 'PRIVILEGE',
       grantee,
       'GRANTED',
       privilege
  FROM dba_tab_privs
 WHERE table_name = '[테이블명]' AND owner = '[스키마명]'
UNION ALL
SELECT 'OBJECT',
       object_name,
       status,
       object_type
  FROM dba_objects
 WHERE object_name = '[테이블명]' AND owner = '[스키마명]'
 ;
 

 -- ===================== LOB사양 확인 =====================
SELECT OWNER,TABLE_NAME,COLUMN_NAME,CACHE,LOGGING,ENCRYPT,COMPRESSION,DEDUPLICATION,SEGMENT_NAME,SECUREFILE
FROM DBA_LOBS 
WHERE TABLE_NAME = '[테이블명]' AND owner = '[스키마명]';
--[스키마명]	[테이블명]	 LOB컬럼1	NO	YES	NO	LOW	NO	SYS_LOB0000271650C00004$$	YES
--[스키마명]	[테이블명]	 LOB컬럼2	NO	YES	NO	NO	NO	SYS_LOB0000271650C00005$$	YES
--[스키마명]	[테이블명]	 LOB컬럼3	NO	YES	NO	LOW	NO	SYS_LOB0000271650C00006$$	YES
--[스키마명]	[테이블명]	 LOB컬럼4	NO	YES	NO	LOW	NO	SYS_LOB0000271650C00007$$	YES



-- ===================== LOB컬럼별 세그먼트 용량=====================
SELECT BYTES /1024 /1024 /1024, A.*
FROM DBA_SEGMENTS A
WHERE TABLESPACE_NAME = '[테이블명]'	
AND SEGMENT_NAME IN ('SYS_LOB0000271650C00004$$', 'SYS_LOB0000271650C00005$$','SYS_LOB0000271650C00006$$', 'SYS_LOB0000271650C00007$$')
ORDER BY 1 desc;
--2.43768310546875	ZZ	SYS_LOB0000271650C00004$$
--0.00311279296875	ZZ	SYS_LOB0000271650C00005$$
--0.00115966796875	ZZ	SYS_LOB0000271650C00006$$
--0.0001220703125	ZZ	SYS_LOB0000271650C00007$$


-- ===================== TEMP(구 원본테이블) 삭제(백업 기간을 두고 보관하였다가 삭제)=====================
DROP TABLE '[테이블명_TEMP]' PURGE;

'ReDefinition' 카테고리의 다른 글

REDEFINITION을 통한 테이블 CLOB타입 COMPRESS (3)	2025.05.21

현재글Redefinition을 이용한 CLOB TO BLOB 컬럼 변환

돼지와함께춤을 님의 블로그

dancewithpig 님의 블로그 입니다.

EXP_OBJ$, SHARED DB LINK, FAST ADD, 2PC PENDING, log miner, DEDICATE DB LINK, Expression Statistics Usage, EXP_STAT$, _add_col_optim_enabled, ORA-03150, DB LINKS, internal error code, 대용량 테이블 컬럼 추가, ORA-02063, WITH GRANT OPTION, qosdExpStatRead, Flashback Versions Query, Oracle, expcnt mismatch, datapump,

Today :
Yesterday :

돼지와함께춤을 님의 블로그