|
|
| (같은 사용자의 중간 판 7개는 보이지 않습니다) |
| 1번째 줄: |
1번째 줄: |
| | == MS MIG 전환 솔루션 == | | == MS MIG 전환 솔루션 == |
| | | | |
| − | === 레파지토리 정보 ===
| + | '''크리에이터 요청으로 자세한건 안보여줌.''' |
| − | ==== MIG 환경설정 ====
| |
| − | # TB_MIG_CONFIG : 데이터 입력, 집계함수 정보 입력 , 환경설정
| |
| − | # TB_MIG_CONFIG_DBOWNER : ( owner 별 db 접속정보/ asis-tobe)
| |
| − | # TB_MIG_CONFIG_USER
| |
| − | # TB_MIG_COD_CLS : 코드분류
| |
| − | # TB_MIG_COD : 코드 , CD008 : 집계함수 항목추가
| |
| − | # TB_MIG_COL_DIC_ASIS : ASIS 컬럼 정보입력 ( asis 딕셔너리)
| |
| − | # TB_MIG_COL_DIC_TOBE : TOBE 컬럼 정보입력 (tobe 딕셔너리) , 신규테이블 추가시
| |
| | | | |
| − | ==== 전환 매핑 관리 ====
| + | [[category:Oracle]] |
| − | # 테이블 매핑
| |
| − | ## TB_MIG_MAP_TBL_USER -- 전환 설계자(담당자) 테이블 매핑시 TB_MIG_MAP_TBL_SCDUL 과 같이 입력
| |
| − | ## TB_MIG_MAP_TBL -- 테이블 전환방식/ 정보 저장/ ... 중요...
| |
| − | ##: TB_MIG_MAP_TBL_HST : 이력
| |
| − | ## TB_MIG_MAP_TBL_SCDUL -- 테이블별 일정관리
| |
| − | ## TB_MIG_MAP_TBL_SUBSET -- 테이블 매핑 설계
| |
| − | ##: TB_MIG_MAP_TBL_SUBSET_HST : 테이블 매핑 설계 이력
| |
| − | ## TB_MIG_MAP_TBL_PGM -- 전환프로그램 컴파일 이력
| |
| − | ## TB_MIG_MAP_TBL_ASIS -- ASIS 테이블 전환방식/ 정보 저장/ ... 중요...
| |
| − | # 컬럼 매핑
| |
| − | ## TB_MIG_MAP_COL : 컬럼매핑설계 , UV_MIG_TOBE_COL_MAP_DEF 관련뷰
| |
| − | ##: TB_MIG_MAP_COL_HST 컬럼매핑 설계 이력
| |
| − | ## TB_MIG_MAP_COL_ASIS : ASIS 매핑 안되는경우 필요
| |
| − | ##: TB_MIG_MAP_COL_ASIS_HST
| |
| − | | |
| − | ==== 전환검증 ====
| |
| − | # TB_MIG_VERIFY_AGG_FUNC -- 집계함수 검증 결과
| |
| − | # TB_MIG_VERIFY_ASIS_ROWS_COUNT -- 일괄검증 후 생성된 로우카운터를 가져온다.
| |
| − | # TB_MIG_VERIFY_TOBE_ROWS_COUNT -- TB_MIG_LOG에 ROW카운트 대체
| |
| − | # TB_MIG_VERIFY_BIZRULE - B/R 검증
| |
| − | # TB_MIG_VERIFY_BIZRULE_RSLT -- B/R 검증 실행 결과 수행이력
| |
| − | # TB_MIG_ASIS_LOAD_VERIFY_COUNT 검증 및 카운트
| |
| − | | |
| − | ==== 전환결과 리포트 ====
| |
| − | # TB_MIG_LOG -- 중요 - 전환결과 로그(전환계/운영계) 임으로 타겟서버에 로그테이블/프로시져 이 필수 존재해야함
| |
| − | | |
| − | ==== 추가기능 ====
| |
| − | # TB_MIG_MAP_TBL_ROWS_SPLIT -- 분리전환 미사용 .
| |
| − | # TB_MIG_CONFIG_SESSION - 불필요 /* 생성보류(미사용 추정) */
| |
| − | # TB_MIG_MAP_AUTOMAP_OBJ -- 미사용 (자동매핑기능) 막혔음.
| |
| − | # TB_MIG_CONFIG_LOAD_TARGET_DB - 불필요
| |
| − | # TB_MIG_VERIFY_TBL_COUNT -- 미사용
| |
| − | | |
| − | ==== 주요 VIEW ====
| |
| − | # 기능 뷰
| |
| − | ## UV_MIG_SUBJECT_AREA
| |
| − | ## UV_MIG_COL_DIC_TOBE
| |
| − | ## UV_MIG_COL_DIC_ASIS
| |
| − | ## UV_MIG_ASIS_COL_MAP_DEF ASIS 오너 추가시 수정해야함
| |
| − | ## UV_MIG_TOBE_COL_MAP_DEF TOBE 오너 추가시 수정해야함
| |
| − | ## UV_MIG_LOG -- 전환 개발 FAIL 내역
| |
| − | ## UV_MIG_LOG_DASHBOARD -- 불필요 추정
| |
| − | # 참조용 뷰
| |
| − | ## MIGCOMMON.ALL_TABLES
| |
| − | ## MIGCOMMON.ALL_TAB_COMMENTS
| |
| − | ## MIGCOMMON.ALL_VIEWS
| |
| − | ## MIGCOMMON.ALL_OBJECTS
| |
| − | ## MIGCOMMON.ALL_TAB_COLUMNS
| |
| − | ## MIGCOMMON.ALL_TAB_COLS
| |
| − | ## MIGCOMMON.ALL_CONS_COLUMNS
| |
| − | ## MIGCOMMON.ALL_CONSTRAINTS
| |
| − | ## MIGCOMMON.ALL_COL_COMMENTS
| |
| − | ## DBA_TAB_PARTITIONS_ASIS -- 빈껍데기만 생성, AS-IS 파티션 있을시 변경할 것.
| |
| − | ## UV_MIG_CONFIG_USER
| |
| − | ## UV_MIG_ASIS_STATS_TOTAL
| |
| − | ## UV_MIG_STATS_SCDUL_PLAN
| |
| − | ## UV_MIG_MAP_TBL_STATS -- OWNER 확인할것.
| |
| − | ## UV_MIG_STATS_SA_OWNER -- OWNER 확인할것.
| |
| − | ## UV_MIG_STATS_SA_OWNER_TOT
| |
| − | ## UV_MIG_VERIFY_ASIS_ROWS_COUNT
| |
| − | # 매핑용 뷰
| |
| − | ## UV_MIG_ASIS_COD_CLS /* AS-IS 공통코드 그룹 */
| |
| − | ## UV_MIG_DATE_CHK /* 날짜 체크 */
| |
| − | ## UV_MIG_FULL_DATE_CHK
| |
| − | ## UV_MIG_FULL_TIME_CHK
| |
| − | ## UV_MIG_VERIFY_STAT
| |
| − | ## UV_MIG_TOBE_COD : (중요)변경필요
| |
| − | ## UV_MIG_ASIS_COD : (중요)변경필요
| |
| − | # 매핑 관련
| |
| − | ## UV_MIG_MAP_COL
| |
| − | ## UV_MIG_MAP_COL_HST
| |
| − | ## UV_MIG_MAP_TBL_PGM
| |
| − | ## UV_MIG_MAP_TBL_SUBSET_HST
| |
| − | ## UV_MIG_MAP_TBL_HST
| |
| − | ## UV_MIG_MAP_TBL_ASIS
| |
| − | ## UV_MIG_MAP_TBL_BASE
| |
| − | ## UV_MIG_MAP_TBL :(중요)
| |
| − | ## UV_MIG_MAP_TBL_DEF : (중요)
| |
| − | ## UV_MIG_MAP_TBL_SUBSET : (중요)
| |
| − | | |
| − | === MIG 레파지토리 테이블/컬럼 정보 일괄 갱신 프로시져 ===
| |
| − | <source lang=sql>
| |
| − | 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;
| |
| − | /
| |
| − | </source>
| |
| − | | |
| − | === 테이블명 변경에 따른 MIG 레파지토리 일괄변경 프로시져 ===
| |
| − | <source lang=sql>
| |
| − | 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;
| |
| − | /
| |
| − | </source>
| |
| − | | |
| − | === 전환매핑 관리 테이블 ===
| |
| − | | |
| − | <source lang=sql>
| |
| − | 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);
| |
| − | | |
| − | </source>
| |