오라클 운영중인 테이블 Loop Commit 작업 스크립트

   

 오라클 운영중인 테이블에 Update/Delete/Insert를 대량으로 해야하는 상황이 있는데, Lock 현상을 피하기 위하여 데이터를 PK Level로 Loop Commit 형태로 작업하게 될때 사용하는 스크립트이다.

 가능하다면 대량작업은 정기점검시점에 서비스 사용이 없을때 인덱스까지 정리한뒤 대량의 데이터 변경 작업을 CTAS로 한뒤, 테이블 교체 및 인덱스를 재생성하여 테이블을 깨끗하게 재생성하는 방향으로 작업되는 것이 가장 좋은거 같다.

 PL/SQL 형태와 Merge into 구문으로 작성된 내역으로 대상 테이블과 조건사항만 변경하여 사용이 가능한 상태이다.

 

[1안]

DECLARE 

    CNT NUMBER;
    
    CURSOR C IS
        SELECT  *
        FROM    EMP  
        WHERE   UP.EMP_DATE = TO_DATE('SYSDATE-1','YYYYMMDDHH24MISS');

BEGIN
    CNT := 0;
    
    FOR CUR IN C LOOP

        DELETE  FROM EMP UP
        WHERE   UP.EMPNO = CUR.EMPNO;

        CNT := CNT + 1;
        
        IF (MOD(CNT, 100) = 0) THEN 
            COMMIT;
        END IF;            

    END LOOP;
    
END;
/

[2안]

MERGE
INTO EMP SI 
USING (
          SELECT  *
          FROM    EMP   UP
          WHERE   UP.EMP_DATE = TO_DATE('SYSDATE-1','YYYYMMDDHH24MISS')
        ) TGT
ON ( SI.EMPNO = TGT.EMPNO )

WHEN  MATCHED THEN 
UPDATE
SET     SI.SAL = '0'

DELETE  WHERE 1 = 1 ;

 

# Oracle running table Loop Commit task script

# Script de tarea de confirmación de bucle de tabla en ejecución de Oracle

# オラクル運営しているテーブルLoop Commitジョブスクリプト

# Oracle रनिंग टेबल लूप कमिट स्क्रिप्ट

# Oracle运行表Loop Commit任务脚本