UPDATE EMAIL 1

1. 변환 조건
컬럼 LIKE '%@%'
SUBSTR(컬럼명, 1, INSTR(컬럼명, '@') - 1)

2. 대상 목록 테이블 만들기
CREATE TABLE USER_ID_UPDATE_TARGET (
    TABLE_NAME  VARCHAR2(128),
    COLUMN_NAME VARCHAR2(128)
);

INSERT INTO USER_ID_UPDATE_TARGET VALUES ('테이블A', 'USER_ID');
INSERT INTO USER_ID_UPDATE_TARGET VALUES ('테이블A', 'CREATOR_ID');
INSERT INTO USER_ID_UPDATE_TARGET VALUES ('테이블A', 'UPDATER_ID');
INSERT INTO USER_ID_UPDATE_TARGET VALUES ('테이블B', 'SUBMIT_USER_ID');
INSERT INTO USER_ID_UPDATE_TARGET VALUES ('테이블C', 'ACCEPT_USER_ID');

-- COMMIT;


3. 먼저 건수 확인
SELECT COUNT(*)
FROM 테이블A
WHERE USER_ID LIKE '%@%';

4. UPDATE 기본 SQL
UPDATE 테이블A
   SET USER_ID = SUBSTR(USER_ID, 1, INSTR(USER_ID, '@') - 1)
WHERE USER_ID LIKE '%@%';

소량 테이블
UPDATE 테이블A
   SET USER_ID = SUBSTR(USER_ID, 1, INSTR(USER_ID, '@') - 1)
WHERE USER_ID LIKE '%@%';

-- COMMIT;

대용량 테이블
UPDATE 테이블A
   SET USER_ID = SUBSTR(USER_ID, 1, INSTR(USER_ID, '@') - 1)
WHERE ROWID BETWEEN :start_id AND :end_id
   AND USER_ID LIKE '%@%';


6. 백업은 “전체 백업”보다 “변경 대상만 백업”

CREATE TABLE BK_테이블A_USER_ID AS
SELECT ROWID AS RID,
       USER_ID AS OLD_USER_ID,
       SUBSTR(USER_ID, 1, INSTR(USER_ID, '@') - 1) AS NEW_USER_ID
FROM 테이블A
WHERE USER_ID LIKE '%@%';

복구
UPDATE 테이블A A
   SET USER_ID = (
       SELECT B.OLD_USER_ID
       FROM BK_테이블A_USER_ID B
       WHERE B.RID = A.ROWID
   )
WHERE A.ROWID IN (
       SELECT RID
       FROM BK_테이블A_USER_ID
);


순서
대상 테이블/컬럼 목록 관리 테이블 생성
컬럼별 COUNT(*) WHERE 컬럼 LIKE '%@%'로 영향 건수 확인
변경 대상만 ROWID + OLD_VALUE + NEW_VALUE로 백업
소량 테이블은 일반 UPDATE
대용량 테이블은 ROWID chunk 단위 배치 또는 DBMS_PARALLEL_EXECUTE
컬럼별 처리 결과를 로그 테이블에 저장
검증 후 인덱스 통계 갱신 검토


핵심 UPDATE 문

UPDATE 대상테이블
   SET 대상컬럼 = SUBSTR(대상컬럼, 1, INSTR(대상컬럼, '@') - 1)
WHERE 대상컬럼 LIKE '%@%';


========================================


-- 1. 새로운 임시 테이블 생성 (원하는 도메인 제외 가공)
CREATE TABLE 테이블A_NEW NOLOGGING AS
SELECT /*+ PARALLEL(테이블A, 4) */  -- 시스템 자원에 맞춰 병렬도 지정
       CASE WHEN USER_ID LIKE '%@apple.com' THEN SUBSTR(USER_ID, 1, INSTR(USER_ID, '@') - 1)
            ELSE USER_ID
       END AS USER_ID,
       CASE WHEN CREATOR_ID LIKE '%@apple.com' THEN SUBSTR(CREATOR_ID, 1, INSTR(CREATOR_ID, '@') - 1)
            ELSE CREATOR_ID
       END AS CREATOR_ID,
       UPDATER_ID -- 변경 없는 컬럼은 그대로 유지
       -- ... 나머지 컬럼들 전부 나열 ...
FROM 테이블A;

-- 2. 기존 테이블 삭제 또는 백업용 이름 변경
ALTER TABLE 테이블A RENAME TO 테이블A_OLD;

-- 3. 신규 테이블을 운영 테이블명으로 변경
ALTER TABLE 테이블A_NEW RENAME TO 테이블A;

-- 4. (필수) 기존 테이블A에 있던 인덱스, 제약조건(PK/FK), 권한(Grant)을 새 테이블에 재생성


====================================

/* 1. 로그 테이블 */
CREATE TABLE USER_ID_UPDATE_LOG (
    LOG_ID        NUMBER GENERATED ALWAYS AS IDENTITY,
    TABLE_NAME    VARCHAR2(128),
    COLUMN_NAME   VARCHAR2(128),
    TARGET_COUNT  NUMBER,
    BACKUP_COUNT  NUMBER,
    UPDATE_COUNT  NUMBER,
    STATUS        VARCHAR2(30),
    ERROR_MSG     VARCHAR2(4000),
    START_DT      DATE,
    END_DT        DATE
);

/* 2. 백업 테이블 */
CREATE TABLE USER_ID_UPDATE_BACKUP (
    TABLE_NAME   VARCHAR2(128),
    COLUMN_NAME  VARCHAR2(128),
    RID          ROWID,
    OLD_VALUE    VARCHAR2(4000),
    NEW_VALUE    VARCHAR2(4000),
    BACKUP_DT    DATE DEFAULT SYSDATE
);






CREATE OR REPLACE PROCEDURE PRC_REMOVE_EMAIL_DOMAIN (
    P_SMALL_LIMIT IN NUMBER DEFAULT 100000,  -- 이 건수 이하면 소량 처리
    P_BATCH_SIZE  IN NUMBER DEFAULT 5000,    -- 커밋 단위
    P_SLEEP_SEC   IN NUMBER DEFAULT 0        -- 배치 사이 쉬는 시간
)
IS
    V_SQL           VARCHAR2(32767);
    V_CNT           NUMBER;
    V_BACKUP_CNT    NUMBER;
    V_UPDATE_CNT    NUMBER;
    V_TOTAL_BACKUP  NUMBER;
    V_TOTAL_UPDATE  NUMBER;
    V_LOG_ID        NUMBER;

    TYPE T_ROWID_TAB IS TABLE OF ROWID;
    V_RIDS T_ROWID_TAB;

    V_TABLE_NAME  VARCHAR2(128);
    V_COLUMN_NAME VARCHAR2(128);

    V_SAFE_TABLE  VARCHAR2(261);
    V_SAFE_COLUMN VARCHAR2(128);

    C_CURSOR SYS_REFCURSOR;
BEGIN
    FOR R IN (
        SELECT TABLE_NAME, COLUMN_NAME
        FROM USER_ID_UPDATE_TARGET
        ORDER BY TABLE_NAME, COLUMN_NAME
    )
    LOOP
        V_TABLE_NAME  := TRIM(R.TABLE_NAME);
        V_COLUMN_NAME := TRIM(R.COLUMN_NAME);

        V_TOTAL_BACKUP := 0;
        V_TOTAL_UPDATE := 0;

        BEGIN
            /*
              동적 SQL 대상 검증.
              DBMS_ASSERT는 동적 SQL에 들어가는 객체명 검증용 Oracle 기본 패키지입니다.
            */
            V_SAFE_TABLE  := DBMS_ASSERT.SQL_OBJECT_NAME(V_TABLE_NAME);
            V_SAFE_COLUMN := DBMS_ASSERT.SIMPLE_SQL_NAME(V_COLUMN_NAME);

            EXECUTE IMMEDIATE
                'SELECT COUNT(*) FROM ' || V_SAFE_TABLE ||
                ' WHERE ' || V_SAFE_COLUMN || ' LIKE ''%@%'''
            INTO V_CNT;

            INSERT INTO USER_ID_UPDATE_LOG (
                TABLE_NAME, COLUMN_NAME, TARGET_COUNT,
                BACKUP_COUNT, UPDATE_COUNT,
                STATUS, START_DT
            )
            VALUES (
                V_TABLE_NAME, V_COLUMN_NAME, V_CNT,
                0, 0,
                'START', SYSDATE
            )
            RETURNING LOG_ID INTO V_LOG_ID;

            COMMIT;

            IF V_CNT = 0 THEN
                UPDATE USER_ID_UPDATE_LOG
                   SET STATUS = 'NO_TARGET',
                       END_DT = SYSDATE
                 WHERE LOG_ID = V_LOG_ID;

                COMMIT;
                CONTINUE;
            END IF;

            /*
              소량/대량 모두 같은 배치 구조 사용.
              차이는 배치 크기만 다르게 줄 수 있음.
              운영 안정성 때문에 소량도 한 번에 전체 UPDATE하지 않음.
            */
            LOOP
                V_SQL :=
                    'SELECT ROWID ' ||
                    '  FROM ' || V_SAFE_TABLE ||
                    ' WHERE ' || V_SAFE_COLUMN || ' LIKE ''%@%'' ' ||
                    '   AND ROWNUM <= :BATCH_SIZE';

                OPEN C_CURSOR FOR V_SQL USING P_BATCH_SIZE;
                FETCH C_CURSOR BULK COLLECT INTO V_RIDS;
                CLOSE C_CURSOR;

                EXIT WHEN V_RIDS.COUNT = 0;

                /*
                  백업 먼저.
                  이미 백업된 ROWID는 중복 백업 방지.
                */
                FORALL I IN 1 .. V_RIDS.COUNT
                    EXECUTE IMMEDIATE
                        'INSERT INTO USER_ID_UPDATE_BACKUP ' ||
                        '       (TABLE_NAME, COLUMN_NAME, RID, OLD_VALUE, NEW_VALUE, BACKUP_DT) ' ||
                        'SELECT :1, :2, ROWID, ' || V_SAFE_COLUMN || ', ' ||
                        '       SUBSTR(' || V_SAFE_COLUMN || ', 1, INSTR(' || V_SAFE_COLUMN || ', ''@'') - 1), ' ||
                        '       SYSDATE ' ||
                        '  FROM ' || V_SAFE_TABLE ||
                        ' WHERE ROWID = :3 ' ||
                        '   AND ' || V_SAFE_COLUMN || ' LIKE ''%@%'' ' ||
                        '   AND NOT EXISTS ( ' ||
                        '       SELECT 1 ' ||
                        '         FROM USER_ID_UPDATE_BACKUP B ' ||
                        '        WHERE B.TABLE_NAME = :4 ' ||
                        '          AND B.COLUMN_NAME = :5 ' ||
                        '          AND B.RID = :6 ' ||
                        '   )'
                    USING V_TABLE_NAME, V_COLUMN_NAME, V_RIDS(I),
                          V_TABLE_NAME, V_COLUMN_NAME, V_RIDS(I);

                V_BACKUP_CNT := SQL%ROWCOUNT;
                V_TOTAL_BACKUP := V_TOTAL_BACKUP + V_BACKUP_CNT;

                /*
                  UPDATE.
                */
                FORALL I IN 1 .. V_RIDS.COUNT
                    EXECUTE IMMEDIATE
                        'UPDATE ' || V_SAFE_TABLE ||
                        '   SET ' || V_SAFE_COLUMN ||
                        '       = SUBSTR(' || V_SAFE_COLUMN || ', 1, INSTR(' || V_SAFE_COLUMN || ', ''@'') - 1) ' ||
                        ' WHERE ROWID = :1 ' ||
                        '   AND ' || V_SAFE_COLUMN || ' LIKE ''%@%'''
                    USING V_RIDS(I);

                V_UPDATE_CNT := SQL%ROWCOUNT;
                V_TOTAL_UPDATE := V_TOTAL_UPDATE + V_UPDATE_CNT;

                UPDATE USER_ID_UPDATE_LOG
                   SET BACKUP_COUNT = V_TOTAL_BACKUP,
                       UPDATE_COUNT = V_TOTAL_UPDATE,
                       STATUS = CASE
                                  WHEN V_CNT <= P_SMALL_LIMIT THEN 'RUNNING_SMALL'
                                  ELSE 'RUNNING_LARGE'
                                END
                 WHERE LOG_ID = V_LOG_ID;

                COMMIT;

                IF P_SLEEP_SEC > 0 THEN
                    DBMS_LOCK.SLEEP(P_SLEEP_SEC);
                END IF;
            END LOOP;

            UPDATE USER_ID_UPDATE_LOG
               SET BACKUP_COUNT = V_TOTAL_BACKUP,
                   UPDATE_COUNT = V_TOTAL_UPDATE,
                   STATUS = 'DONE',
                   END_DT = SYSDATE
             WHERE LOG_ID = V_LOG_ID;

            COMMIT;

        EXCEPTION
            WHEN OTHERS THEN
                ROLLBACK;

                INSERT INTO USER_ID_UPDATE_LOG (
                    TABLE_NAME, COLUMN_NAME, TARGET_COUNT,
                    BACKUP_COUNT, UPDATE_COUNT,
                    STATUS, ERROR_MSG, START_DT, END_DT
                )
                VALUES (
                    V_TABLE_NAME, V_COLUMN_NAME, V_CNT,
                    V_TOTAL_BACKUP, V_TOTAL_UPDATE,
                    'ERROR', SQLERRM, SYSDATE, SYSDATE
                );

                COMMIT;
        END;
    END LOOP;
END;
/





실행 예시
BEGIN
    PRC_REMOVE_EMAIL_DOMAIN(
        P_SMALL_LIMIT => 100000,
        P_BATCH_SIZE  => 5000,
        P_SLEEP_SEC   => 1
    );
END;
/



검증:
SELECT *
FROM USER_ID_UPDATE_LOG
ORDER BY LOG_ID;

SELECT TABLE_NAME, COLUMN_NAME, COUNT(*)
FROM USER_ID_UPDATE_BACKUP
GROUP BY TABLE_NAME, COLUMN_NAME;



복구:
UPDATE 테이블A A
   SET USER_ID = (
       SELECT B.OLD_VALUE
       FROM USER_ID_UPDATE_BACKUP B
       WHERE B.TABLE_NAME = '테이블A'
         AND B.COLUMN_NAME = 'USER_ID'
         AND B.RID = A.ROWID
   )
WHERE A.ROWID IN (
       SELECT B.RID
       FROM USER_ID_UPDATE_BACKUP B
       WHERE B.TABLE_NAME = '테이블A'
         AND B.COLUMN_NAME = 'USER_ID'
);

COMMIT;



==========
복구

CREATE OR REPLACE PROCEDURE PRC_RESTORE_EMAIL_DOMAIN (
    P_TABLE_NAME IN VARCHAR2,
    P_BATCH_SIZE IN NUMBER DEFAULT 5000,
    P_SLEEP_SEC  IN NUMBER DEFAULT 0
)
IS
    V_SAFE_TABLE   VARCHAR2(261);
    V_SAFE_COLUMN  VARCHAR2(128);
    V_SQL          VARCHAR2(32767);
    V_UPDATE_CNT   NUMBER;
    V_TOTAL_CNT    NUMBER := 0;

    TYPE T_RID_TAB IS TABLE OF ROWID;
    V_RIDS T_RID_TAB;

    C_CURSOR SYS_REFCURSOR;
BEGIN
    V_SAFE_TABLE := DBMS_ASSERT.SQL_OBJECT_NAME(TRIM(P_TABLE_NAME));

    FOR C IN (
        SELECT DISTINCT COLUMN_NAME
        FROM USER_ID_UPDATE_BACKUP
        WHERE TABLE_NAME = TRIM(P_TABLE_NAME)
        ORDER BY COLUMN_NAME
    )
    LOOP
        V_SAFE_COLUMN := DBMS_ASSERT.SIMPLE_SQL_NAME(C.COLUMN_NAME);

        LOOP
            V_SQL :=
                'SELECT B.RID ' ||
                '  FROM USER_ID_UPDATE_BACKUP B ' ||
                ' WHERE B.TABLE_NAME = :1 ' ||
                '   AND B.COLUMN_NAME = :2 ' ||
                '   AND ROWNUM <= :3 ' ||
                '   AND EXISTS ( ' ||
                '       SELECT 1 ' ||
                '         FROM ' || V_SAFE_TABLE || ' T ' ||
                '        WHERE T.ROWID = B.RID ' ||
                '          AND T.' || V_SAFE_COLUMN || ' <> B.OLD_VALUE ' ||
                '   )';

            OPEN C_CURSOR FOR V_SQL
                USING TRIM(P_TABLE_NAME), C.COLUMN_NAME, P_BATCH_SIZE;

            FETCH C_CURSOR BULK COLLECT INTO V_RIDS;
            CLOSE C_CURSOR;

            EXIT WHEN V_RIDS.COUNT = 0;

            FORALL I IN 1 .. V_RIDS.COUNT
                EXECUTE IMMEDIATE
                    'UPDATE ' || V_SAFE_TABLE || ' T ' ||
                    '   SET T.' || V_SAFE_COLUMN || ' = ( ' ||
                    '       SELECT B.OLD_VALUE ' ||
                    '         FROM USER_ID_UPDATE_BACKUP B ' ||
                    '        WHERE B.TABLE_NAME = :1 ' ||
                    '          AND B.COLUMN_NAME = :2 ' ||
                    '          AND B.RID = T.ROWID ' ||
                    '   ) ' ||
                    ' WHERE T.ROWID = :3'
                USING TRIM(P_TABLE_NAME), C.COLUMN_NAME, V_RIDS(I);

            V_UPDATE_CNT := SQL%ROWCOUNT;
            V_TOTAL_CNT := V_TOTAL_CNT + V_UPDATE_CNT;

            COMMIT;

            IF P_SLEEP_SEC > 0 THEN
                DBMS_LOCK.SLEEP(P_SLEEP_SEC);
            END IF;
        END LOOP;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('복구 완료. 총 복구 건수: ' || V_TOTAL_CNT);
END;
/






확인



SELECT TABLE_NAME, COLUMN_NAME, COUNT(*)
FROM USER_ID_UPDATE_BACKUP
WHERE TABLE_NAME = '테이블A'
GROUP BY TABLE_NAME, COLUMN_NAME;