MS MIG 전환솔루션
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 MS MIG 전환 솔루션[편집]
1.1 레파지토리 정보[편집]
1.1.1 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 딕셔너리) , 신규테이블 추가시
1.1.2 전환 매핑 관리[편집]
- 테이블 매핑
- 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_MAP_COL : 컬럼매핑설계 , UV_MIG_TOBE_COL_MAP_DEF 관련뷰
1.1.3 전환검증[편집]
- 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 검증 및 카운트
1.1.4 전환결과 리포트[편집]
- TB_MIG_LOG -- 중요 - 전환결과 로그(전환계/운영계) 임으로 타겟서버에 로그테이블/프로시져 이 필수 존재해야함
1.1.5 추가기능[편집]
- 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 -- 미사용
1.1.6 주요 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 : (중요)
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);1.4.1 전환매핑 관리 이력 테이블[편집]
-- 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(100 BYTE),
MAP_TBL_MGR_HST_IDX NUMBER,
CHANGE_DT DATE,
HST_GUBUN 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_매핑관리_이력"."구분" IS '구분';