ReDefinition

REDEFINITION을 통한 테이블 CLOB타입 COMPRESS

돼지와함께춤을 2025. 5. 21. 09:12

NON COMPRESS였던 CLOB컬럼을 가진 테이블에서 CLOB를 COMPRESS작업하는 과정이다.

 

테스트 이유는 용량 모니터링 중 하루에 30GB 데이터파일을 2개씩이나 잡아먹는 말도안되는 용량 증폭 테이블이 발견

개발팀과 접촉 후 알아보니 히스토리를 배치성으로 저장하지만 보관 기준에 벗어난 데이터를 삭제해야 하는데 

잘 안되어 발생한 케이스이다..

 

뭐 본론으로 돌아가서 LOB타입에 컬럼은 생각 이상으로 용량을 잡아먹으니

NON COMPRESS 컬럼은 COMPRESS 옵션을 줘야한다고 판단.

 

배치성으로 INSERT되는 테이블이지만 운영 테이블이니 REDEFINITION 전략이 좋은것같다 요약하면

  1. 시작전 임시테이블에 데이터를 수용할 수 있는 용량을 확인
  2. COMPRESSION패키지를 통한 압축 효율 검토
  3. LOB타입을 COMPRESS으로 같은 메타에 임시 테이블을 만들기
  4. REDEF패키지를 통해 REDEFINITION 시작
  5. 테스트용 DML 스크립트 실행하여 계속해서 데이터 삽입 
  6. REDEF 메타정보 복사
  7. FINISH전 SYNC
  8. FINISH
  9. 검증

  • LOB컬럼의 세그먼트 이름을 확인한다(테이블 일반 컬럼과는 다른 세그먼트 공간을 활용)
-- ===================== LOB사양 확인 =====================
SELECT OWNER,TABLE_NAME,COLUMN_NAME,CACHE,LOGGING,ENCRYPT,COMPRESSION,DEDUPLICATION,SEGMENT_NAME,SECUREFILE
FROM DBA_LOBS 
WHERE OWNER = '[스키마명]'
  AND TABLE_NAME = '[테이블명]';
--[스키마명]	[테이블명]	    [CLOB컬럼명1]	    NO	YES	NO	NO	NO	SYS_LOB0000254467C00004$$	YES
--[스키마명]	[테이블명]	    [CLOB컬럼명2]	    NO	YES	NO	NO	NO	SYS_LOB0000254467C00005$$	YES
--[스키마명]	[테이블명]	    [CLOB컬럼명3]	    NO	YES	NO	NO	NO	SYS_LOB0000254467C00006$$	YES
--[스키마명]	[테이블명]	    [CLOB컬럼명4]	    NO	YES	NO	NO	NO	SYS_LOB0000254467C00007$$	YES

 

 

  • LOB컬럼의 용량을 확인한다
-- ===================== LOB컬럼별 세그먼트 용량=====================
SELECT BYTES /1024 /1024 /1024, A.*
FROM DBA_SEGMENTS A
WHERE TABLESPACE_NAME = '[해당 테이블이 속한 테이블스페이스명]'
AND SEGMENT_NAME IN ('SYS_LOB0000254467C00004$$', 'SYS_LOB0000254467C00005$$','SYS_LOB0000254467C00006$$', 'SYS_LOB0000254467C00007$$')
ORDER BY 1 desc;
--5.67205810546875	[스키마명] 	SYS_LOB0000254467C00004$$
--0.00408935546875	[스키마명] 	SYS_LOB0000254467C00005$$
--0.00213623046875	[스키마명] 	SYS_LOB0000254467C00006$$
--0.0001220703125	[스키마명] 	SYS_LOB0000254467C00007$$

 

 

  • 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 사용가능해보임

 

 

  • 압축 효율 검토 / LOB컬럼2는 효과가 없어 압축대상 제외
-- ===================== 압축 효율 검토 =====================
-- 분석대상 테이블만큼의 공간을 전부 사용하나요?	❌ 아닙니다 일부 샘플 데이터만 복사
-- 공간 얼마나 필요하나요?	            보통 수 MB~수십 MB. 큰 테이블이라도 GB 단위로 쓰는 경우는 드뭅니다
-- 분석 끝나고 자동 정리되나요?	            네, 자동 정리됩니다 (임시 테스트 테이블 삭제됨)
-- 공간 부족하면?	                    ❌ 오류 발생 → 전용 테이블스페이스 만들어 지정하면 됨
-- comptype                          DBMS_COMPRESSION.COMP_FOR_LOW / DBMS_COMPRESSION.COMP_FOR_HIGH / DBMS_COMPRESSION.COMP_FOR_MEDIUM
DECLARE
    blkcnt_cmp     PLS_INTEGER;
    blkcnt_uncmp   PLS_INTEGER;
    lobcnt         PLS_INTEGER;
    cmp_ratio      NUMBER;
    comptype_str   VARCHAR2(100);
BEGIN
  DBMS_COMPRESSION.GET_COMPRESSION_RATIO (
      scratchtbsname    => '[테이블스페이스명]',
      tabowner          => '[스키마명]',
      tabname           => '[테이블명]',
      lobname           => '[대상LOB컬럼명]',
      partname          => NULL,
      comptype          => DBMS_COMPRESSION.COMP_LOB_LOW,
      blkcnt_cmp        => blkcnt_cmp,
      blkcnt_uncmp      => blkcnt_uncmp,
      lobcnt            => lobcnt,
      cmp_ratio         => cmp_ratio,
      comptype_str      => comptype_str,
      subset_numrows    => 10000  
  );

  DBMS_OUTPUT.PUT_LINE('압축 방식: ' || comptype_str);
  DBMS_OUTPUT.PUT_LINE('압축률(예상): ' || ROUND(cmp_ratio, 2));
  DBMS_OUTPUT.PUT_LINE('압축 전 블록 수: ' || blkcnt_uncmp);
  DBMS_OUTPUT.PUT_LINE('압축 후 블록 수: ' || blkcnt_cmp);
  DBMS_OUTPUT.PUT_LINE('LOB 건수: ' || lobcnt);
END;
-- COL         LOW     HIGH
-- LOB컬럼1     5.1배    7.8배
-- LOB컬럼2     1배      0.9배
-- LOB컬럼3     4.6배    6.7배
-- LOB컬럼4     2.3배    3.2배

 

 

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

 

 

  • INTERIM 테이블 생성
-- ===================== INTERIM_TABLE 생성 // 제약조건(NOT NULL 포함), 트리거, 인덱스, 권한 등 종속객체 제외하고 생성=====================
CREATE TABLE 테이블_TEMP
(
    CD         VARCHAR2(3)  ,
    ID             VARCHAR2(10) ,
    NO              NUMBER(10)  ,
    LOB컬럼1            CLOB  ,
    LOB컬럼2            CLOB  ,
    LOB컬럼3            CLOB,
    LOB컬럼4            CLOB,
    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
    COMPRESS LOW
)
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
    COMPRESS LOW
)
LOB (LOB컬럼4) STORE AS SECUREFILE 
(
    STORAGE
    (
        INITIAL 104K
        NEXT 1M
    )
    CHUNK 8192
    PCTVERSION 0
    COMPRESS LOW
);

 

 

  • REDEFINITION 실행 중 데이터 손실 테스트용 스크립트
-- ===================== 데이터 손실 테스트 다른 세션에서 실시간 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,             
            'LOB 2 : ' || i,             
            'LOB 2 : ' || i,             
            'LOB 2 : ' || i,            
            'LJG',                         
            SYSTIMESTAMP,                    
            'LJG',                         
            SYSTIMESTAMP                     
        );

        COMMIT;

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

 

 

  • REDEFINITION 시작
-- ===================== REDEF START =====================
BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname        => '[스키마명]',
    orig_table   => '[테이블명]',
    int_table    => '[INTERIM테이블명]',
--    col_mapping => 'COL1 COL1, COL2 COL2, COL3 COL3, UTL_RAW.CAST_TO_RAW(COL4) COL4'      컬럼매핑 시 캐스팅 가능 // 쓸거면 모든 컬럼 매핑 명시가 필요..
    options_flag => DBMS_REDEFINITION.CONS_USE_PK
  );
END;
/


-- REDEF 시작 여부 확인
SELECT * FROM DBA_REDEFINITION_OBJECTS WHERE BASE_TABLE_NAME = '[테이블명]';

 

 

  • 종속객체 복사
-- ===================== REDEF 제약조건, 인덱스, 트리거, 권한 복사=====================
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;
/

 

 

 

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

 

 

  • REDEF 종료
-- ===================== SYNC 후 빠르게 FINISH =====================
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 [테이블명]

-- ===================== 데이터 손실 확인 =====================
SELECT INST_CD,PID,NO,FST_RGSTR_ID,FST_RGST_TS,LAST_UPDTR_ID,LAST_UPDT_TS FROM [테이블명] WHERE INST_CD = 'LJG' ORDER BY NO;

-- ===================== 통계 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_LOB0000270854C00004$$	YES
--[스키마명]	[테이블명]	    LOB컬럼2	NO	YES	NO	NO	NO	SYS_LOB0000270854C00005$$	YES
--[스키마명]	[테이블명]	    LOB컬럼3	NO	YES	NO	LOW	NO	SYS_LOB0000270854C00006$$	YES
--[스키마명]	[테이블명]	    LOB컬럼4	NO	YES	NO	LOW	NO	SYS_LOB0000270854C00007$$	YES


-- ===================== LOB컬럼별 세그먼트 용량=====================
SELECT BYTES /1024 /1024 /1024, A.*
FROM DBA_SEGMENTS A
WHERE TABLESPACE_NAME = '[테이블명]'	
AND SEGMENT_NAME IN ('SYS_LOB0000270854C00004$$', 'SYS_LOB0000270854C00005$$','SYS_LOB0000270854C00006$$', 'SYS_LOB0000270854C00007$$')
ORDER BY 1 desc;
--0.21893310546875	[스키마명] 	SYS_LOB0000270854C00004$$
--0.00506591796875	[스키마명] 	SYS_LOB0000270854C00005$$
--0.0001220703125	[스키마명] 	SYS_LOB0000270854C00007$$
--0.0001220703125	[스키마명] 	SYS_LOB0000270854C00006$$

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

'ReDefinition' 카테고리의 다른 글

Redefinition을 이용한 CLOB TO BLOB 컬럼 변환  (0) 2025.05.21

'ReDefinition'의 다른글

  • 현재글REDEFINITION을 통한 테이블 CLOB타입 COMPRESS

관련글

티스토리툴바