-- 1. 테이블 비교
-- : day2서버를 기준으로 day2서버에 있는 테이블이 mig서버에는 존재안하는지 체크한다.
--
ex)
SELECT *
FROM (SELECT A.TABLE_NAME AS BAK_DAY2_TABLE_NAME,
B.TABLE_NAME AS N_MIGDB_TABLE_NAME
FROM (SELECT TABLE_NAME
FROM DBA_TABLES@TTOO
WHERE OWNER IN( 'MVOC', 'SVOCRUN' )
-- AND ( SUBSTR(TABLE_NAME,1,3) IN ('AUD','CDC','COM','SDA','SFA' ,'STR') OR TABLE_NAME IN ('CAMP_RESULT_NEW','CONTRACT_NEW','ORA_DDL_LOG','QST_CASE','QST_RESULT_NEW','QUESTIONS','SHEET','TARGET_NEW','TBMBRNC','TCCEMPL','TCCPROD') )
) A -- 업무영역 수정
LEFT OUTER JOIN (SELECT TABLE_NAME
FROM SYS.DBA_TABLES
WHERE OWNER IN( 'MVOC', 'SVOCRUN' )
-- AND TABLE_NAME LIKE 'N@_%' ESCAPE '@' -- AND ( SUBSTR(TABLE_NAME,1,3) IN ('AUD','CDC','COM','SDA','SFA' ,'STR') OR TABLE_NAME IN ('CAMP_RESULT_NEW','CONTRACT_NEW','ORA_DDL_LOG','QST_CASE','QST_RESULT_NEW','QUESTIONS','SHEET','TARGET_NEW','TBMBRNC','TCCEMPL','TCCPROD') );
) B -- 업무영역 수정
ON A.TABLE_NAME = B.TABLE_NAME)
WHERE ( BAK_DAY2_TABLE_NAME IS NULL
OR N_MIGDB_TABLE_NAME IS NULL )
ORDER BY 1, 2;
-- 2. 컬럼비교(누락된 컬럼찾기)
-- :
SELECT *
FROM
(
SELECT A.TABLE_NAME AS BAK_DAY2_TABLE_NAME,
A.COLUMN_NAME AS BAK_DAY2_COLUMN_NAME,
A.DATA_TYPE AS BAK_DAY2_DATA_TYPE,
CASE
WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_SCALE > 0 THEN A.DATA_PRECISION ||',' ||A.DATA_SCALE
WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_SCALE = 0 THEN TO_CHAR(A.DATA_PRECISION)
ELSE TO_CHAR(A.DATA_LENGTH)
END AS BAK_DAY2_DATA_LENGTH,
B.TABLE_NAME AS N_MIGDB_TABLE_NAME,
B.COLUMN_NAME AS N_MIGDB_COLUMN_NAME,
B.DATA_TYPE AS N_MIGDB_DATA_TYPE,
CASE
WHEN B.DATA_TYPE = 'NUMBER' AND B.DATA_SCALE > 0 THEN B.DATA_PRECISION ||',' ||B.DATA_SCALE
WHEN B.DATA_TYPE = 'NUMBER' AND B.DATA_SCALE = 0 THEN TO_CHAR(B.DATA_PRECISION)
ELSE TO_CHAR(B.DATA_LENGTH)
END AS N_MIGDB_DATA_LENGTH
FROM (SELECT TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
COLUMN_ID,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE
FROM DBA_TAB_COLUMNS@TTOO
WHERE OWNER IN ( 'MVOC', 'SVOCRUN' )
-- AND ( SUBSTR(TABLE_NAME,1,3) IN ('AUD','CDC','COM','SDA','SFA' ,'STR') OR TABLE_NAME IN ('CAMP_RESULT_NEW','CONTRACT_NEW','ORA_DDL_LOG','QST_CASE','QST_RESULT_NEW','QUESTIONS','SHEET','TARGET_NEW','TBMBRNC','TCCEMPL','TCCPROD') ) -- 업무영역 수정
) A
LEFT OUTER JOIN (SELECT TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
COLUMN_ID,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE
FROM DBA_TAB_COLUMNS
WHERE OWNER IN ( 'MVOC', 'SVOCRUN' )
--AND TABLE_NAME LIKE 'N@_%' ESCAPE '@'
-- AND ( SUBSTR(TABLE_NAME,1,3) IN ('AUD','CDC','COM','SDA','SFA' ,'STR') OR TABLE_NAME IN ('CAMP_RESULT_NEW','CONTRACT_NEW','ORA_DDL_LOG','QST_CASE','QST_RESULT_NEW','QUESTIONS','SHEET','TARGET_NEW','TBMBRNC','TCCEMPL','TCCPROD') )
) B -- 업무영역 수정
ON A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
) A
WHERE ( NVL(BAK_DAY2_TABLE_NAME, 'NA') <> NVL(N_MIGDB_TABLE_NAME, 'NA')
OR NVL(BAK_DAY2_COLUMN_NAME, 'NA') <> NVL(N_MIGDB_COLUMN_NAME, 'NA') )
ORDER BY BAK_DAY2_TABLE_NAME,
BAK_DAY2_COLUMN_NAME;
-- 3. 컬럼 비교 (데이터타입/LENGTH/NULLABLE 비교)
SELECT
ORGDB_OWNER
,ORGDB_TABLE_NAME
,ORGDB_COLUMN_NAME
,ORGDB_DATA_TYPE
,ORGDB_NULLABLE
,ORGDB_DATA_LENGTH
,MIGDB_COLUMN_NAME
,MIGDB_DATA_TYPE
,MIGDB_NULLABLE
,MIGDB_DATA_LENGTH
,DIFF_TP
,DIFF_LENGTH
FROM
(
SELECT CASE
WHEN ORGDB_DATA_TYPE <> MIGDB_DATA_TYPE THEN 'DATATYPE'
WHEN ORGDB_NULLABLE <> MIGDB_NULLABLE THEN 'NULLABLE'
WHEN ORGDB_DATA_LENGTH <> MIGDB_DATA_LENGTH THEN CASE WHEN ORGDB_CHAR_LENGTH <> MIGDB_CHAR_LENGTH OR MIGDB_CHAR_LENGTH <> MIGDB_DATA_LENGTH THEN 'DATALENGTH' END
END AS DIFF_TP,
CASE
WHEN ORGDB_TABLE_NAME = MIGDB_TABLE_NAME
AND ORGDB_COLUMN_NAME = MIGDB_COLUMN_NAME
AND ORGDB_DATA_TYPE = MIGDB_DATA_TYPE
AND TO_NUMBER(ORGDB_DATA_LENGTH) > TO_NUMBER(MIGDB_DATA_LENGTH) THEN 'ORG_BIG'
WHEN ORGDB_TABLE_NAME = MIGDB_TABLE_NAME
AND ORGDB_COLUMN_NAME = MIGDB_COLUMN_NAME
AND ORGDB_DATA_TYPE = MIGDB_DATA_TYPE
AND TO_NUMBER(ORGDB_DATA_LENGTH) < TO_NUMBER(MIGDB_DATA_LENGTH) THEN 'MIG_BIG'
END AS DIFF_LENGTH,
A.*
FROM (SELECT A.TABLE_NAME AS ORGDB_TABLE_NAME,
A.COLUMN_NAME AS ORGDB_COLUMN_NAME,
REPLACE(A.DATA_TYPE, 'VARCHAR2', 'VARCHAR') AS ORGDB_DATA_TYPE,
A.NULLABLE AS ORGDB_NULLABLE,
CASE
WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_SCALE > 0 THEN A.DATA_PRECISION ||',' ||A.DATA_SCALE
WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_SCALE = 0 THEN TO_CHAR(A.DATA_PRECISION)
ELSE TO_CHAR(A.DATA_LENGTH)
END AS ORGDB_DATA_LENGTH,
B.TABLE_NAME AS MIGDB_TABLE_NAME,
B.COLUMN_NAME AS MIGDB_COLUMN_NAME,
B.DATA_TYPE AS MIGDB_DATA_TYPE,
B.NULLABLE AS MIGDB_NULLABLE,
CASE
WHEN B.DATA_TYPE = 'NUMBER' AND B.DATA_SCALE > 0 THEN B.DATA_PRECISION ||',' ||B.DATA_SCALE
WHEN B.DATA_TYPE = 'NUMBER' AND B.DATA_SCALE = 0 THEN TO_CHAR(B.DATA_PRECISION)
ELSE TO_CHAR(B.DATA_LENGTH)
END AS MIGDB_DATA_LENGTH,
A.CHAR_LENGTH AS ORGDB_CHAR_LENGTH,
B.CHAR_LENGTH AS MIGDB_CHAR_LENGTH,
A.OWNER AS ORGDB_OWNER
FROM (SELECT TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
COLUMN_ID,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE,
CHAR_LENGTH,
OWNER
FROM DBA_TAB_COLUMNS@TTOO
WHERE OWNER IN ( 'MVOC', 'SVOCRUN' )
-- AND ( SUBSTR(TABLE_NAME,1,3) IN ('AUD','CDC','COM','SDA','SFA' ,'STR') OR TABLE_NAME IN ('CAMP_RESULT_NEW','CONTRACT_NEW','ORA_DDL_LOG','QST_CASE','QST_RESULT_NEW','QUESTIONS','SHEET','TARGET_NEW','TBMBRNC','TCCEMPL','TCCPROD') ) -- 업무영역 수정
) A
LEFT JOIN (SELECT TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
COLUMN_ID,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE,
CHAR_LENGTH,
OWNER
FROM DBA_TAB_COLUMNS
WHERE OWNER IN ( 'MVOC', 'SVOCRUN' )
-- AND TABLE_NAME LIKE 'N@_%' ESCAPE '@'
-- AND ( SUBSTR(TABLE_NAME,1,3) IN ('AUD','CDC','COM','SDA','SFA' ,'STR') OR TABLE_NAME IN ('CAMP_RESULT_NEW','CONTRACT_NEW','ORA_DDL_LOG','QST_CASE','QST_RESULT_NEW','QUESTIONS','SHEET','TARGET_NEW','TBMBRNC','TCCEMPL','TCCPROD') )
) B -- 업무영역 수정
ON A.OWNER = B.OWNER AND A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME) A
WHERE ( ORGDB_DATA_TYPE <> MIGDB_DATA_TYPE
OR ORGDB_DATA_LENGTH <> MIGDB_DATA_LENGTH
OR ORGDB_NULLABLE <> MIGDB_NULLABLE )
AND ORGDB_DATA_TYPE NOT IN ( 'DATE', 'FLOAT' )
AND ORGDB_DATA_TYPE NOT LIKE 'TIMESTAMP%'
AND ORGDB_TABLE_NAME NOT IN (SELECT OBJECT_NAME
FROM DBA_OBJECTS
WHERE OBJECT_TYPE IN ( 'VIEW' ))
)
WHERE DIFF_TP IS NOT NULL
ORDER BY ORGDB_OWNER,
ORGDB_TABLE_NAME,
ORGDB_COLUMN_NAME;
--4) 누락된 인덱스 찾기
SELECT *
FROM (SELECT A.TABLE_NAME AS BAK_DAY2_TABLE_NAME,
A.INDEX_NAME AS BAK_DAY2_INDEX_NAME,
B.TABLE_NAME AS N_MIGDB_TABLE_NAME,
B.INDEX_NAME AS N_MIGDB_INDEX_NAME
FROM (SELECT TABLE_NAME,
INDEX_NAME
FROM DBA_INDEXES@TTOO
WHERE OWNER IN ( 'MVOC', 'SVOCRUN' )
AND INDEX_TYPE <> 'LOB'
-- AND ( SUBSTR(TABLE_NAME,1,3) IN ('AUD','CDC','COM','SDA','SFA' ,'STR') OR TABLE_NAME IN ('CAMP_RESULT_NEW','CONTRACT_NEW','ORA_DDL_LOG','QST_CASE','QST_RESULT_NEW','QUESTIONS','SHEET','TARGET_NEW','TBMBRNC','TCCEMPL','TCCPROD') )
) A -- 업무영역 수정
LEFT OUTER JOIN (SELECT TABLE_NAME,
INDEX_NAME
FROM DBA_INDEXES
WHERE OWNER IN ( 'MVOC', 'SVOCRUN' )
-- AND TABLE_NAME LIKE 'N@_%' ESCAPE '@'
-- AND ( SUBSTR(TABLE_NAME,1,3) IN ('AUD','CDC','COM','SDA','SFA' ,'STR') OR TABLE_NAME IN ('CAMP_RESULT_NEW','CONTRACT_NEW','ORA_DDL_LOG','QST_CASE','QST_RESULT_NEW','QUESTIONS','SHEET','TARGET_NEW','TBMBRNC','TCCEMPL','TCCPROD') )
) B -- 업무영역 수정
ON A.TABLE_NAME = B.TABLE_NAME
AND A.INDEX_NAME = B.INDEX_NAME)
WHERE ( BAK_DAY2_INDEX_NAME IS NULL
OR N_MIGDB_INDEX_NAME IS NULL )
ORDER BY 1,2;
--5) 인덱스의 컬럼 비교하기
-- 수정이 필요해 보임
SELECT *
FROM
(
SELECT A.TABLE_NAME AS BAK_DAY2_TABLE_NAME, A.INDEX_NAME AS BAK_DAY2_INDEX_NAME,
A.COLUMN_NAME AS BAK_DAY2_COLUMN_NAME, A.COLUMN_POSITION AS BAK_DAY2_COLUMN_POSITION,
A.COLUMN_LENGTH AS BAK_DAY2_COLUMN_LENGTH, A.CHAR_LENGTH AS BAK_DAY2_CHAR_LENGTH,
A.DESCEND AS BAK_DAY2_DESCEND ,
B.TABLE_NAME AS N_MIGDB_TABLE_NAME, B.INDEX_NAME AS N_MIGDB_INDEX_NAME,
B.COLUMN_NAME AS N_MIGDB_COLUMN_NAME, B.COLUMN_POSITION AS N_MIGDB_COLUMN_POSITION,
B.COLUMN_LENGTH AS N_MIGDB_COLUMN_LENGTH,
-- B.CHAR_LENGTH AS N_MIGDB_CHAR_LENGTH,
B.DESCEND AS N_MIGDB_DESCEND
FROM (SELECT
TABLE_NAME, INDEX_NAME, COLUMN_NAME, COLUMN_POSITION, COLUMN_LENGTH, CHAR_LENGTH, DESCEND
FROM DBA_IND_COLUMNS@TTOO
WHERE INDEX_OWNER IN ( 'MVOC', 'SVOCRUN' )
-- AND ( SUBSTR(TABLE_NAME,1,3) IN ('AUD','CDC','COM','SDA','SFA' ,'STR') OR TABLE_NAME IN ('CAMP_RESULT_NEW','CONTRACT_NEW','ORA_DDL_LOG','QST_CASE','QST_RESULT_NEW','QUESTIONS','SHEET','TARGET_NEW','TBMBRNC','TCCEMPL','TCCPROD') )
) A -- 업무영역 수정
LEFT OUTER JOIN (SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, COLUMN_POSITION AS COLUMN_POSITION , COLUMN_LENGTH, DESCEND
FROM DBA_IND_COLUMNS
WHERE INDEX_OWNER IN ( 'MVOC', 'SVOCRUN' )
-- AND ( SUBSTR(TABLE_NAME,1,3) IN ('AUD','CDC','COM','SDA','SFA' ,'STR') OR TABLE_NAME IN ('CAMP_RESULT_NEW','CONTRACT_NEW','ORA_DDL_LOG','QST_CASE','QST_RESULT_NEW','QUESTIONS','SHEET','TARGET_NEW','TBMBRNC','TCCEMPL','TCCPROD') )
) B -- 업무영역 수정
ON A.TABLE_NAME = B.TABLE_NAME
AND A.INDEX_NAME = B.INDEX_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
AND A.COLUMN_POSITION = B.COLUMN_POSITION
AND A.COLUMN_LENGTH = B.COLUMN_LENGTH
-- AND A.CHAR_LENGTH = B.CHAR_LENGTH
AND A.DESCEND = B.DESCEND
)
WHERE (BAK_DAY2_INDEX_NAME IS NULL
OR N_MIGDB_INDEX_NAME IS NULL)
-- AND BAK_DAY2_TABLE_NAME = 'CRWL_DOC'
ORDER BY 1, 2;
-- 6. object 비교 (누락된 object 찾기)
SELECT *
FROM (SELECT A.OBJECT_TYPE AS BAK_DAY2_OBJECT_TYPE,
A.OBJECT_NAME AS BAK_DAY2_OBJECT_NAME,
B.OBJECT_TYPE AS N_MIGDB_OBJECT_TYPE,
B.OBJECT_NAME AS N_MIGDB_OBJECT_NAME
FROM (SELECT OBJECT_TYPE,
OBJECT_NAME
FROM DBA_OBJECTS@TTOO
WHERE OWNER IN ( 'MVOC', 'SVOCRUN' )) A
LEFT OUTER JOIN (SELECT OBJECT_TYPE,
OBJECT_NAME
FROM DBA_OBJECTS
WHERE OWNER IN ( 'MVOC', 'SVOCRUN' )) B
ON A.OBJECT_TYPE = B.OBJECT_TYPE
AND A.OBJECT_NAME = B.OBJECT_NAME)
WHERE NVL(BAK_DAY2_OBJECT_NAME, 'NA') NOT LIKE 'O@_%' ESCAPE '@'
AND ( BAK_DAY2_OBJECT_NAME IS NULL
OR N_MIGDB_OBJECT_NAME IS NULL )
ORDER BY 1,2;