행위

MS MIG 전환솔루션

DB CAFE

Dbcafe (토론 | 기여)님의 2020년 11월 25일 (수) 11:31 판
thumb_up 추천메뉴 바로가기


1 MS MIG 전환 솔루션[편집]

1.1 레파지토리 정보[편집]

1.1.1 MIG 환경설정[편집]

  1. TB_MIG_CONFIG  : 데이터 입력, 집계함수 정보 입력 , 환경설정
  2. TB_MIG_CONFIG_DBOWNER  : ( owner 별 db 접속정보/ asis-tobe)
  3. TB_MIG_CONFIG_USER
  4. TB_MIG_COD_CLS  : 코드분류
  5. TB_MIG_COD : 코드 , CD008 : 집계함수 항목추가
  6. TB_MIG_COL_DIC_ASIS : ASIS 컬럼 정보입력 ( asis 딕셔너리)
  7. TB_MIG_COL_DIC_TOBE : TOBE 컬럼 정보입력 (tobe 딕셔너리) , 신규테이블 추가시

1.1.2 전환 매핑 관리[편집]

  1. 테이블 매핑
    1. TB_MIG_MAP_TBL_USER -- 전환 설계자(담당자) 테이블 매핑시 TB_MIG_MAP_TBL_SCDUL 과 같이 입력
    2. TB_MIG_MAP_TBL -- 테이블 전환방식/ 정보 저장/ ... 중요...
      TB_MIG_MAP_TBL_HST  : 이력
    3. TB_MIG_MAP_TBL_SCDUL -- 테이블별 일정관리
    4. TB_MIG_MAP_TBL_SUBSET -- 테이블 매핑 설계
      TB_MIG_MAP_TBL_SUBSET_HST  : 테이블 매핑 설계 이력
    5. TB_MIG_MAP_TBL_PGM -- 전환프로그램 컴파일 이력
    6. TB_MIG_MAP_TBL_ASIS -- ASIS 테이블 전환방식/ 정보 저장/ ... 중요...
  2. 컬럼 매핑
    1. TB_MIG_MAP_COL  : 컬럼매핑설계 , UV_MIG_TOBE_COL_MAP_DEF 관련뷰
      TB_MIG_MAP_COL_HST 컬럼매핑 설계 이력
    2. TB_MIG_MAP_COL_ASIS  : ASIS 매핑 안되는경우 필요
      TB_MIG_MAP_COL_ASIS_HST

1.1.3 전환검증[편집]

  1. TB_MIG_VERIFY_AGG_FUNC -- 집계함수 검증 결과
  2. TB_MIG_VERIFY_ASIS_ROWS_COUNT -- 일괄검증 후 생성된 로우카운터를 가져온다.
  3. TB_MIG_VERIFY_TOBE_ROWS_COUNT -- TB_MIG_LOG에 ROW카운트 대체
  4. TB_MIG_VERIFY_BIZRULE - B/R 검증
  5. TB_MIG_VERIFY_BIZRULE_RSLT -- B/R 검증 실행 결과 수행이력
  6. TB_MIG_ASIS_LOAD_VERIFY_COUNT 검증 및 카운트

1.1.4 전환결과 리포트[편집]

  1. TB_MIG_LOG -- 중요 - 전환결과 로그(전환계/운영계) 임으로 타겟서버에 로그테이블/프로시져 이 필수 존재해야함

1.1.5 추가기능[편집]

  1. TB_MIG_MAP_TBL_ROWS_SPLIT -- 분리전환 미사용 .
  2. TB_MIG_CONFIG_SESSION - 불필요 /* 생성보류(미사용 추정) */
  3. TB_MIG_MAP_AUTOMAP_OBJ -- 미사용 (자동매핑기능) 막혔음.
  4. TB_MIG_CONFIG_LOAD_TARGET_DB - 불필요
  5. TB_MIG_VERIFY_TBL_COUNT -- 미사용

1.1.6 주요 VIEW[편집]

  1. 기능 뷰
    1. UV_MIG_SUBJECT_AREA
    2. UV_MIG_COL_DIC_TOBE
    3. UV_MIG_COL_DIC_ASIS
    4. UV_MIG_ASIS_COL_MAP_DEF ASIS 오너 추가시 수정해야함
    5. UV_MIG_TOBE_COL_MAP_DEF TOBE 오너 추가시 수정해야함
    6. UV_MIG_LOG -- 전환 개발 FAIL 내역
    7. UV_MIG_LOG_DASHBOARD -- 불필요 추정
  2. 참조용 뷰
    1. MIGCOMMON.ALL_TABLES
    2. MIGCOMMON.ALL_TAB_COMMENTS
    3. MIGCOMMON.ALL_VIEWS
    4. MIGCOMMON.ALL_OBJECTS
    5. MIGCOMMON.ALL_TAB_COLUMNS
    6. MIGCOMMON.ALL_TAB_COLS
    7. MIGCOMMON.ALL_CONS_COLUMNS
    8. MIGCOMMON.ALL_CONSTRAINTS
    9. MIGCOMMON.ALL_COL_COMMENTS
    10. DBA_TAB_PARTITIONS_ASIS -- 빈껍데기만 생성, AS-IS 파티션 있을시 변경할 것.
    11. UV_MIG_CONFIG_USER
    12. UV_MIG_ASIS_STATS_TOTAL
    13. UV_MIG_STATS_SCDUL_PLAN
    14. UV_MIG_MAP_TBL_STATS -- OWNER 확인할것.
    15. UV_MIG_STATS_SA_OWNER -- OWNER 확인할것.
    16. UV_MIG_STATS_SA_OWNER_TOT
    17. UV_MIG_VERIFY_ASIS_ROWS_COUNT
  3. 매핑용 뷰
    1. UV_MIG_ASIS_COD_CLS /* AS-IS 공통코드 그룹 */
    2. UV_MIG_DATE_CHK /* 날짜 체크 */
    3. UV_MIG_FULL_DATE_CHK
    4. UV_MIG_FULL_TIME_CHK
    5. UV_MIG_VERIFY_STAT
    6. UV_MIG_TOBE_COD : (중요)변경필요
    7. UV_MIG_ASIS_COD : (중요)변경필요
  4. 매핑 관련
    1. UV_MIG_MAP_COL
    2. UV_MIG_MAP_COL_HST
    3. UV_MIG_MAP_TBL_PGM
    4. UV_MIG_MAP_TBL_SUBSET_HST
    5. UV_MIG_MAP_TBL_HST
    6. UV_MIG_MAP_TBL_ASIS
    7. UV_MIG_MAP_TBL_BASE
    8. UV_MIG_MAP_TBL :(중요)
    9. UV_MIG_MAP_TBL_DEF : (중요)
    10. UV_MIG_MAP_TBL_SUBSET : (중요)

1.2 MIG 레파지토리 테이블/컬럼 정보 일괄 갱신 프로시져[편집]

CREATE OR REPLACE PROCEDURE MIGCOMMON.SP_TOBE_TAB_REFRESH
/* MIG_TOBE_TABLE 갱신 프로시져 BY CYKIM (2020/11/03) 
*  -- 0. 사용법
* EXEC SP_TOBE_TAB_REFRESH();
* 
* -- 1.오너 전체 테이블 리플레쉬   
* EXEC SP_TOBE_TAB_REFRESH(IN_OWNER=>'TBA', IN_TABLE_NAME=>NULL);
*
* -- 2.오너별, 개별테이블  리플레쉬
* EXEC SP_TOBE_TAB_REFRESH(IN_OWNER=>'오너명', IN_TABLE_NAME=>'TABLE명');
*
*/
(
    IN_OWNER IN VARCHAR2 DEFAULT NULL,
    IN_TABLE_NAME IN VARCHAR2 DEFAULT NULL
)

AUTHID CURRENT_USER 

IS

    CURSOR REFRESH_OBJECT IS
        SELECT A.OWNER,A.TABLE_NAME
          FROM ALL_TABLES A                
         WHERE A.OWNER = IN_OWNER 
           AND A.TABLE_NAME=IN_TABLE_NAME   
    ;  

    V_D_SQL  VARCHAR2(200);                                                  
    V_SQL  VARCHAR2(32767);
    
    V_OWNER  VARCHAR2(100);
    V_TABLE_NAME  VARCHAR2(100);

    
    V_MSG       long;    
    
BEGIN

    IF IN_OWNER IS NULL THEN 
        DBMS_OUTPUT.ENABLE(100); 
        DBMS_OUTPUT.PUT_LINE('usage: ');
        DBMS_OUTPUT.PUT_LINE('SP_TOBE_TAB_REFRESH(IN_OWNER    => '''' , IN_TABLE_NAME   => ''''); ');
                  
        RETURN; 
    END IF; 
    
    DBMS_OUTPUT.ENABLE(32767);
    IF V_TABLE_NAME IS NOT NULL THEN
        FOR V_ROW IN REFRESH_OBJECT
        LOOP   

            BEGIN 
                    
                V_OWNER:= V_ROW.OWNER;
                V_TABLE_NAME:= V_ROW.TABLE_NAME;
                DBMS_OUTPUT.ENABLE(32767);
               
              IF V_TABLE_NAME IS NOT NULL THEN
                -- OWNER.개별 테이블 갱신 처리  
                V_D_SQL :='DELETE FROM MIGCOMMON.TB_MIG_COL_DIC_TOBE WHERE OWNER='''||V_ROW.OWNER||''' AND TABLE_NAME='''||V_ROW.TABLE_NAME||'''';
                  V_SQL :='INSERT INTO MIGCOMMON.TB_MIG_COL_DIC_TOBE (OWNER,TABLE_NAME,LOGI_NAME,COLUMN_ID,COLUMN_NAME,ATTR_NAME,DATA_TYPE,CONV_COL_LEN,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE,KEY,COL_CRT_TYPE)
                           SELECT OWNER,TABLE_NAME,LOGI_NAME,COLUMN_ID,COLUMN_NAME,ATTR_NAME,DATA_TYPE,NULL AS CONV_COL_LEN,NULL AS DATA_LENGTH,NULL AS DATA_PRECISION,NULL AS DATA_SCALE,NULL AS NULLABLE,NULL AS KEY,''가상컬럼''     AS COL_CRT_TYPE
                             FROM TB_MIG_COL_DIC_TOBE_DUMMY
                             WHERE OWNER = '''||V_ROW.OWNER||'''
                               AND TABLE_NAME = '''||V_ROW.TABLE_NAME||''' 
                            UNION ALL
                            SELECT TB.OWNER,
                                   TB.TABLE_NAME,
                                   SUBSTR (LOGI.COMMENTS, 1, 40)    AS LOGI_NAME,
                                   COL.COLUMN_ID,
                                   COL.COLUMN_NAME,
                                   SUBSTR (ATTR.COMMENTS, 1, 40)    AS ATTR_NAME,
                                   COL.DATA_TYPE,
                                   CASE
                                       WHEN COL.DATA_TYPE IN (''VARCHAR2'', ''CHAR'')
                                       THEN
                                           TO_CHAR (COL.DATA_LENGTH)
                                       WHEN     COL.DATA_TYPE IN (''NUMBER'')
                                            AND COL.DATA_PRECISION IS NOT NULL
                                       THEN
                                              TO_CHAR (COL.DATA_PRECISION)
                                           || DECODE (COL.DATA_SCALE, ''0'', '''', '''','''' || COL.DATA_SCALE)
                                       ELSE
                                           ''''
                                   END                              AS CONV_COL_LEN,
                                   COL.DATA_LENGTH,
                                   COL.DATA_PRECISION,
                                   COL.DATA_SCALE,
                                   COL.NULLABLE,
                                   CON.KEY,
                                   ''물리컬럼''                   AS COL_CRT_TYPE
                              FROM MIGCOMMON.ALL_TABLES  TB
                                   INNER JOIN MIGCOMMON.ALL_TAB_COLUMNS COL
                                       ON TB.OWNER = COL.OWNER AND TB.TABLE_NAME = COL.TABLE_NAME
                                   LEFT OUTER JOIN
                                   (  SELECT CCL.OWNER,
                                             CCL.TABLE_NAME,
                                             COLUMN_NAME,
                                             CASE
                                                 WHEN     SUM (DECODE (CONSTRAINT_TYPE, ''P'', 1, 0)) > 0
                                                      AND SUM (DECODE (CONSTRAINT_TYPE, ''R'', 1, 0)) > 0
                                                 THEN
                                                     ''PK,FK''
                                                 WHEN SUM (DECODE (CONSTRAINT_TYPE, ''P'', 1, 0)) > 0
                                                 THEN
                                                     ''PK''
                                                 WHEN SUM (DECODE (CONSTRAINT_TYPE, ''R'', 1, 0)) > 0
                                                 THEN
                                                     ''FK''
                                                 ELSE
                                                     ''''
                                             END    AS KEY
                                        FROM MIGCOMMON.ALL_CONS_COLUMNS CCL,
                                             MIGCOMMON.ALL_CONSTRAINTS CNS
                                       WHERE CCL.CONSTRAINT_NAME = CNS.CONSTRAINT_NAME
                                    GROUP BY CCL.OWNER, CCL.TABLE_NAME, COLUMN_NAME) CON
                                       ON     TB.OWNER = CON.OWNER
                                          AND TB.TABLE_NAME = CON.TABLE_NAME
                                          AND COL.COLUMN_NAME = CON.COLUMN_NAME
                                   LEFT OUTER JOIN MIGCOMMON.ALL_COL_COMMENTS ATTR
                                       ON     ATTR.OWNER = TB.OWNER
                                          AND ATTR.TABLE_NAME = TB.TABLE_NAME
                                          AND ATTR.COLUMN_NAME = COL.COLUMN_NAME
                                   LEFT OUTER JOIN MIGCOMMON.ALL_TAB_COMMENTS LOGI
                                       ON LOGI.OWNER = TB.OWNER AND LOGI.TABLE_NAME = TB.TABLE_NAME
                             WHERE     (TB.OWNER IN
                                            (SELECT DB_OWNER_NM
                                               FROM (SELECT DB_OWNER_NM
                                                       FROM MIGCOMMON.TB_MIG_CONFIG_DBOWNER
                                                      WHERE DB_TARGET_CD = ''2''
                                                     UNION ALL
                                                     SELECT ''MIGCOMMON'' AS DB_OWNER_NM FROM DUAL
                                                     UNION ALL
                                                     SELECT ''MIGBASEDATA'' AS DB_OWNER_NM FROM DUAL)))
                                   AND TB.OWNER = '''||V_ROW.OWNER||'''                             
                                   AND TB.TABLE_NAME = '''||V_ROW.TABLE_NAME||'''';
--                  DBMS_OUTPUT.PUT_LINE('DEL_SQL:'||V_D_SQL);
                  EXECUTE IMMEDIATE V_D_SQL;          
--                  DBMS_OUTPUT.PUT_LINE('INS_SQL:'||V_SQL);
                  EXECUTE IMMEDIATE V_SQL;
                       
                END IF;
            EXCEPTION
                WHEN OTHERS THEN
                   V_MSG := 'ERROR : ['|| to_char(SQLCODE) ||']'|| substr(SQLERRM,1,500); 
                   DBMS_OUTPUT.PUT_LINE('ERR_SQL:'||V_SQL);
                    
             END;
        END LOOP;
                
    ELSE
        -- 해당 오너의 전체 테이블 REFRESH
        V_D_SQL :='DELETE FROM MIGCOMMON.TB_MIG_COL_DIC_TOBE WHERE OWNER='''||IN_OWNER||'''';
          V_SQL :='INSERT INTO MIGCOMMON.TB_MIG_COL_DIC_TOBE (OWNER,TABLE_NAME,LOGI_NAME,COLUMN_ID,COLUMN_NAME,ATTR_NAME,DATA_TYPE,CONV_COL_LEN,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE,KEY,COL_CRT_TYPE)
                   SELECT OWNER,TABLE_NAME,LOGI_NAME,COLUMN_ID,COLUMN_NAME,ATTR_NAME,DATA_TYPE,NULL AS CONV_COL_LEN,NULL AS DATA_LENGTH,NULL AS DATA_PRECISION,NULL AS DATA_SCALE,NULL AS NULLABLE,NULL AS KEY,''가상컬럼''     AS COL_CRT_TYPE
                     FROM TB_MIG_COL_DIC_TOBE_DUMMY
                     WHERE OWNER = '''||IN_OWNER||'''                            
                    UNION ALL
                    SELECT TB.OWNER,
                           TB.TABLE_NAME,
                           SUBSTR (LOGI.COMMENTS, 1, 40)    AS LOGI_NAME,
                           COL.COLUMN_ID,
                           COL.COLUMN_NAME,
                           SUBSTR (ATTR.COMMENTS, 1, 40)    AS ATTR_NAME,
                           COL.DATA_TYPE,
                           CASE
                               WHEN COL.DATA_TYPE IN (''VARCHAR2'', ''CHAR'')
                               THEN
                                   TO_CHAR (COL.DATA_LENGTH)
                               WHEN     COL.DATA_TYPE IN (''NUMBER'')
                                    AND COL.DATA_PRECISION IS NOT NULL
                               THEN
                                      TO_CHAR (COL.DATA_PRECISION)
                                   || DECODE (COL.DATA_SCALE, ''0'', '''', '''','''' || COL.DATA_SCALE)
                               ELSE
                                   ''''
                           END                              AS CONV_COL_LEN,
                           COL.DATA_LENGTH,
                           COL.DATA_PRECISION,
                           COL.DATA_SCALE,
                           COL.NULLABLE,
                           CON.KEY,
                           ''물리컬럼''                   AS COL_CRT_TYPE
                      FROM MIGCOMMON.ALL_TABLES  TB
                           INNER JOIN MIGCOMMON.ALL_TAB_COLUMNS COL
                               ON TB.OWNER = COL.OWNER AND TB.TABLE_NAME = COL.TABLE_NAME
                           LEFT OUTER JOIN
                           (  SELECT CCL.OWNER,
                                     CCL.TABLE_NAME,
                                     COLUMN_NAME,
                                     CASE
                                         WHEN     SUM (DECODE (CONSTRAINT_TYPE, ''P'', 1, 0)) > 0
                                              AND SUM (DECODE (CONSTRAINT_TYPE, ''R'', 1, 0)) > 0
                                         THEN
                                             ''PK,FK''
                                         WHEN SUM (DECODE (CONSTRAINT_TYPE, ''P'', 1, 0)) > 0
                                         THEN
                                             ''PK''
                                         WHEN SUM (DECODE (CONSTRAINT_TYPE, ''R'', 1, 0)) > 0
                                         THEN
                                             ''FK''
                                         ELSE
                                             ''''
                                     END    AS KEY
                                FROM MIGCOMMON.ALL_CONS_COLUMNS CCL,
                                     MIGCOMMON.ALL_CONSTRAINTS CNS
                               WHERE CCL.CONSTRAINT_NAME = CNS.CONSTRAINT_NAME
                            GROUP BY CCL.OWNER, CCL.TABLE_NAME, COLUMN_NAME) CON
                               ON     TB.OWNER = CON.OWNER
                                  AND TB.TABLE_NAME = CON.TABLE_NAME
                                  AND COL.COLUMN_NAME = CON.COLUMN_NAME
                           LEFT OUTER JOIN MIGCOMMON.ALL_COL_COMMENTS ATTR
                               ON     ATTR.OWNER = TB.OWNER
                                  AND ATTR.TABLE_NAME = TB.TABLE_NAME
                                  AND ATTR.COLUMN_NAME = COL.COLUMN_NAME
                           LEFT OUTER JOIN MIGCOMMON.ALL_TAB_COMMENTS LOGI
                               ON LOGI.OWNER = TB.OWNER AND LOGI.TABLE_NAME = TB.TABLE_NAME
                     WHERE     (TB.OWNER IN
                                    (SELECT DB_OWNER_NM
                                       FROM (SELECT DB_OWNER_NM
                                               FROM MIGCOMMON.TB_MIG_CONFIG_DBOWNER
                                              WHERE DB_TARGET_CD = ''2''
                                             UNION ALL
                                             SELECT ''MIGCOMMON'' AS DB_OWNER_NM FROM DUAL
                                             UNION ALL
                                             SELECT ''MIGBASEDATA'' AS DB_OWNER_NM FROM DUAL)))
                           AND TB.OWNER = '''||IN_OWNER||'''';          
                                         
--          DBMS_OUTPUT.PUT_LINE('DEL_SQL:'||V_D_SQL);
          EXECUTE IMMEDIATE V_D_SQL;          
--          DBMS_OUTPUT.PUT_LINE('INS_SQL:'||V_SQL);
          EXECUTE IMMEDIATE V_SQL;
          
          -- LOG 기록 
--          INSERT INTO ZB_MGR_TAB_REFRESH_LOG 
--                      (OWNER , TABLE_NAME) 
--               VALUES (V_ROW.OWNER,V_ROW.TABLE_NAME);
      COMMIT;
                                 
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);

END;
/

1.3 테이블명 변경에 따른 MIG 레파지토리 일괄변경 프로시져[편집]

CREATE OR REPLACE PROCEDURE MIGCOMMON.SP_TOBE_TAB_RENAME
/* MIG_TOBE_TABLE 갱신 프로시져 BY CYKIM (2020/11/03) 
*  -- 0. 사용법
* EXEC SP_TOBE_TAB_RENAME('오너명','OLD_TABLE명','NEW_TABLE명'));
*
* -- 1.오너, 테이블  RENAME
* EXEC SP_TOBE_TAB_RENAME(IN_OWNER=>'오너명', IN_OLD_TABLE=>'OLD_TABLE명', IN_NEW_TABLE=>'NEW_TABLE명');
*
*/
(
    IN_OWNER IN VARCHAR2 DEFAULT NULL,
    IN_OLD_TABLE IN VARCHAR2 DEFAULT NULL,
    IN_NEW_TABLE IN VARCHAR2 DEFAULT NULL    
)

--AUTHID CURRENT_USER 

IS

PRAGMA AUTONOMOUS_TRANSACTION;

--    CURSOR RENAME_OBJECT IS
--        SELECT A.OWNER,A.TABLE_NAME
--          FROM ALL_TABLES A                
--         WHERE A.OWNER = IN_OWNER 
--           AND A.TABLE_NAME=IN_OLD_TABLE   
--    ;  

    V_1_SQL  VARCHAR2(2000);                                                  
    V_2_SQL  VARCHAR2(2000);
    V_FK1_SQL1 VARCHAR2(2000);
    V_FK1_SQL2 VARCHAR2(2000);
    V_FK1_SQL3 VARCHAR2(2000);        
    V_FK2_SQL1 VARCHAR2(2000);
    V_FK2_SQL2 VARCHAR2(2000);
    V_FK2_SQL3 VARCHAR2(2000);        
    
--    V_OWNER  VARCHAR2(100);
--    V_TABLE_NAME  VARCHAR2(100);
    
    V_MSG       long;    
    
BEGIN

    IF IN_OWNER IS NULL THEN 
        DBMS_OUTPUT.ENABLE(200); 
        DBMS_OUTPUT.PUT_LINE('usage: ');
        DBMS_OUTPUT.PUT_LINE('SP_TOBE_TAB_RENAME(IN_OWNER    => '''' , IN_OLD_TABLE   => '''', IN_NEW_TABLE   => ''''); ');
                  
        RETURN; 
    END IF; 
    
    DBMS_OUTPUT.ENABLE(32767);
    
    BEGIN 

--        V_OWNER:= IN_OWNER;
--        V_TABLE_NAME:= IN_OLD_TABLE;
--        V_TABLE_NAME2:= IN_NEW_TABLE;
        DBMS_OUTPUT.ENABLE(32767);

        IF IN_NEW_TABLE IS NOT NULL THEN
        
            -- 1-1.F/K 비활성화 
            V_FK1_SQL1 := 'ALTER TABLE TB_MIG_MAP_TBL_SUBSET DISABLE CONSTRAINTS FK_MIG_MAP_TBL_SUBSET_01';
            V_FK1_SQL2 := 'ALTER TABLE TB_MIG_MAP_TBL_USER DISABLE CONSTRAINTS FK_TB_MIG_MAP_TBL_USER_01';
            V_FK1_SQL3 := 'ALTER TABLE TB_MIG_MAP_TBL_SCDUL DISABLE CONSTRAINTS FK_TB_MIG_MAP_TBL_SCDUL_01';
            
            -- 1-2.F/K 활성화                         
            V_FK2_SQL1 := 'ALTER TABLE TB_MIG_MAP_TBL_SUBSET ENABLE CONSTRAINTS FK_MIG_MAP_TBL_SUBSET_01';
            V_FK2_SQL2 := 'ALTER TABLE TB_MIG_MAP_TBL_USER ENABLE CONSTRAINTS FK_TB_MIG_MAP_TBL_USER_01';
            V_FK2_SQL3 := 'ALTER TABLE TB_MIG_MAP_TBL_SCDUL ENABLE CONSTRAINTS FK_TB_MIG_MAP_TBL_SCDUL_01';    

            --                  DBMS_OUTPUT.PUT_LINE('V_FK1_SQL:'||V_FK1_SQL);
            EXECUTE IMMEDIATE V_FK1_SQL1;      
            EXECUTE IMMEDIATE V_FK1_SQL2;            
            EXECUTE IMMEDIATE V_FK1_SQL3;            
            
            -- 매핑테이블 UPDATE 처리  
            UPDATE TB_MIG_MAP_TBL A SET TOBE_TBL_NM=IN_NEW_TABLE  WHERE TOBE_TBL_NM=IN_OLD_TABLE;
             
            -- 매핑테이블 이력 UPDATE 처리
            UPDATE TB_MIG_MAP_TBL_HST A SET TOBE_TBL_NM=IN_NEW_TABLE  WHERE TOBE_TBL_NM=IN_OLD_TABLE;

            -- 매핑컬럼 UPDATE 처리
            UPDATE TB_MIG_MAP_COL A SET A.TOBE_TBL_NM=IN_NEW_TABLE WHERE A.TOBE_TBL_NM=IN_OLD_TABLE;

            -- 매핑테이블 이력  UPDATE 처리 
            UPDATE TB_MIG_MAP_COL_HST A SET A.TOBE_TBL_NM=IN_NEW_TABLE WHERE A.TOBE_TBL_NM=IN_OLD_TABLE;

            -- 매핑테이블 상세 매핑정보   UPDATE 처리
            UPDATE TB_MIG_MAP_TBL_SUBSET A SET TOBE_TBL_NM=IN_NEW_TABLE WHERE TOBE_TBL_NM=IN_OLD_TABLE;
             
            -- 매핑테이블 상세 매핑정보 이력  UPDATE 처리 
            UPDATE TB_MIG_MAP_TBL_SUBSET_HST A SET TOBE_TBL_NM=IN_NEW_TABLE WHERE TOBE_TBL_NM=IN_OLD_TABLE;
              
            -- 매핑 테이블 담당자 UPDATE 
            UPDATE TB_MIG_MAP_TBL_USER A SET TOBE_TBL_NM=IN_NEW_TABLE WHERE TOBE_TBL_NM=IN_OLD_TABLE;
             
            -- 매핑 테이블 담당자 스케줄 UPDATE  
            UPDATE TB_MIG_MAP_TBL_SCDUL A SET TOBE_TBL_NM=IN_NEW_TABLE WHERE TOBE_TBL_NM=IN_OLD_TABLE;

            -- F/K ENABLE   
            --                  DBMS_OUTPUT.PUT_LINE('INS_SQL:'||V_FK1_SQL);
            COMMIT;
            
            EXECUTE IMMEDIATE V_FK2_SQL1;
            EXECUTE IMMEDIATE V_FK2_SQL2;
            EXECUTE IMMEDIATE V_FK2_SQL3;                        
                
            
            
        END IF;
        
        EXCEPTION
            WHEN OTHERS THEN
            V_MSG := 'ERROR : ['|| to_char(SQLCODE) ||']'|| substr(SQLERRM,1,500); 
--            DBMS_OUTPUT.PUT_LINE('ERR_SQL:'||V_SQL);

              COMMIT;
    END;
        
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);

END;
/

1.4 전환매핑 관리 테이블[편집]

DROP TABLE MIGCOMMON."TB_매핑관리" CASCADE CONSTRAINTS;

CREATE TABLE MIGCOMMON."TB_매핑관리"
(
  NO              NUMBER(10)                    NOT NULL,
  TGT_OWNER       VARCHAR2(200 BYTE),
  "TGT테이블명_영문"    VARCHAR2(200 BYTE),
  "TGT엔터티명_한글"    VARCHAR2(200 BYTE)            NOT NULL,
  UNION_ALL       VARCHAR2(200 BYTE),
  SRC_OWNER       VARCHAR2(200 BYTE),
  "SRC테이블명_영문"    VARCHAR2(200 BYTE),
  "SRC엔터티명_한글"    VARCHAR2(200 BYTE),
  "ASIS매핑"        VARCHAR2(200 BYTE),
  "엑셀업로드"         VARCHAR2(200 BYTE),
  "ASIS재집계"       VARCHAR2(200 BYTE),
  "중간임시테이블"       VARCHAR2(200 BYTE),
  "초기적재"          VARCHAR2(200 BYTE),
  "부연설명"          VARCHAR2(200 BYTE),
  "전환방식_부연설명"     VARCHAR2(200 BYTE),
  "클린징여부"         VARCHAR2(200 BYTE),
  "클린징부연설명"       VARCHAR2(200 BYTE),
  "사전전환여부"        VARCHAR2(200 BYTE),
  "사전전환후_미변경"     VARCHAR2(200 BYTE),
  "사전전환기준컬럼"      VARCHAR2(200 BYTE),
  "사전전환기준컬럼포맷"    VARCHAR2(200 BYTE),
  "선오픈대상여부"       VARCHAR2(200 BYTE),
  "선오픈후_본전환시_제외"  VARCHAR2(200 BYTE),
  ACCESS_PATH     VARCHAR2(200 BYTE),
  JOIN_ON         VARCHAR2(200 BYTE),
  "WHERE절"        VARCHAR2(200 BYTE),
  SQL_WITH_CTE    VARCHAR2(200 BYTE),
  "담당자"           VARCHAR2(200 BYTE),
  "주제영역"          VARCHAR2(200 BYTE),
  "작업상태"          VARCHAR2(200 BYTE),
  "건수"            VARCHAR2(200 BYTE),
  "사이즈"           VARCHAR2(200 BYTE),
  "전환대상여부"        VARCHAR2(200 BYTE),
  "수정자"           VARCHAR2(200 BYTE),
  "비고내용"          VARCHAR2(500 BYTE),
  "파티셔닝_컬럼"       VARCHAR2(20 BYTE)
)
TABLESPACE TS_MIGCOMMON_D01
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
MONITORING;

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리".NO IS '순번';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리".TGT_OWNER IS 'TGT_OWNER';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."TGT테이블명_영문" IS 'TGT테이블명_영문';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."TGT엔터티명_한글" IS 'TGT엔터티명_한글';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리".UNION_ALL IS 'UNION_ALL';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리".SRC_OWNER IS 'SRC_OWNER';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."SRC테이블명_영문" IS 'SRC테이블명_영문';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."SRC엔터티명_한글" IS 'SRC_엔터티명_한글';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."ASIS매핑" IS 'ASIS매핑';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."엑셀업로드" IS '엑셀업로드';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."ASIS재집계" IS 'ASIS재집계';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."중간임시테이블" IS '중간임시테이블';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."초기적재" IS '초기적재';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."부연설명" IS '부연설명';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."전환방식_부연설명" IS '전환방식_부연설명';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."클린징여부" IS '클린징여부';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."클린징부연설명" IS '클린징부연설명';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."사전전환여부" IS '사전전환여부';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."사전전환후_미변경" IS '사전전환후 미변경';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."사전전환기준컬럼" IS '사전전환기준컬럼';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."사전전환기준컬럼포맷" IS '사전전환기준컬럼포맷';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."선오픈대상여부" IS '선오픈대상여부';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."선오픈후_본전환시_제외" IS '선오픈후 본전환시제외';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리".ACCESS_PATH IS 'Access Path';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리".JOIN_ON IS 'JOIN_ON';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."WHERE절" IS 'WHERE';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리".SQL_WITH_CTE IS 'SQL WITH_CTE';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."담당자" IS '담당자';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."주제영역" IS '주제영역';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."작업상태" IS '작업상태';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."건수" IS '건수';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."사이즈" IS '사이즈';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."전환대상여부" IS '전환대상여부';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."수정자" IS '수정자';

COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."비고내용" IS '비고내용';


CREATE UNIQUE INDEX MIGCOMMON."PK_매핑관리" ON MIGCOMMON."TB_매핑관리"
(NO)
LOGGING
TABLESPACE TS_MIGCOMMON_D01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

CREATE OR REPLACE TRIGGER MIGCOMMON.TG_MIG_MAP_MGR_01
  AFTER INSERT OR UPDATE OR DELETE
  ON MIGCOMMON."TB_매핑관리"
  FOR EACH ROW
DECLARE 
    VAR_HST_GUBUN VARCHAR2(100);
BEGIN    
    
    IF INSERTING THEN 
       VAR_HST_GUBUN := '추가';
    ELSIF UPDATING THEN 
       VAR_HST_GUBUN := '수정';
    ELSIF DELETING THEN 
       VAR_HST_GUBUN := '삭제';
    END IF; 
    
    IF VAR_HST_GUBUN = '추가' THEN /* 제외하면 ERROR 발생 */
       RETURN;
    END IF;
    
    INSERT INTO MIGCOMMON."TB_매핑관리_이력"
    (
          MAP_TBL_MGR_HST_IDX         
        , CHANGE_DT                 
        , HST_GUBUN           
----------
,"NO"
,"TGT_OWNER"
,"TGT테이블명_영문"
,"TGT엔터티명_한글"
,"UNION_ALL"
,"SRC_OWNER"
,"SRC테이블명_영문"
,"SRC_엔터티명_한글"
,"ASIS매핑"
,"엑셀업로드"
,"ASIS재집계"
,"중간임시테이블"
,"초기적재"
,"부연설명"
,"전환방식_부연설명"
,"클린징여부"
,"클린징부연설명"
,"사전전환여부"
,"사전전환후_미변경"
,"사전전환기준컬럼"
,"사전전환기준컬럼포맷"
,"선오픈대상여부"
,"선오픈후_본전환시_제외"
,"ACCESS_PATH"
,"JOIN_ON"
,"WHERE절"
,"SQL_WITH_CTE"
,"담당자"
,"주제영역"
,"작업상태"
,"건수"
,"사이즈"
,"전환대상여부"
,"수정자"
,"비고내용"
,"구분"
     )
    VALUES
    (   
        MIGCOMMON.SQ_MIG_MAP_MGR_HST.NEXTVAL
        , SYSDATE
        , VAR_HST_GUBUN
-- ------------------------
,:OLD."NO"
,:OLD."TGT_OWNER"
,:OLD."TGT테이블명_영문"
,:OLD."TGT엔터티명_한글"
,:OLD."UNION_ALL"
,:OLD."SRC_OWNER"
,:OLD."SRC테이블명_영문"
,:"OLD.SRC_엔터티명_한글"
,:OLD."ASIS매핑"
,:OLD."엑셀업로드"
,:OLD."ASIS재집계"
,:OLD."중간임시테이블"
,:OLD."초기적재"
,:OLD."부연설명"
,:OLD."전환방식_부연설명"
,:OLD."클린징여부"
,:OLD."클린징부연설명"
,:OLD."사전전환여부"
,:OLD."사전전환후_미변경"
,:OLD."사전전환기준컬럼"
,:OLD."사전전환기준컬럼포맷"
,:OLD."선오픈대상여부"
,:OLD."선오픈후_본전환시_제외"
,:OLD."ACCESS_PATH"
,:OLD."JOIN_ON"
,:OLD."WHERE절"
,:OLD."SQL_WITH_CTE"
,:OLD."담당자"
,:OLD."주제영역"
,:OLD."작업상태"
,:OLD."건수"
,:OLD."사이즈"
,:OLD."전환대상여부"
,:OLD."수정자"
,:OLD."비고내용"
,:"OLD.구분"
    );  
    
END;
/


ALTER TABLE MIGCOMMON."TB_매핑관리" ADD (
  CONSTRAINT "PK_매핑관리"
  PRIMARY KEY
  (NO)
  USING INDEX MIGCOMMON."PK_매핑관리"
  ENABLE VALIDATE);