[SQL] 원격 서버 데이터 비교 자동 스크립트(PL/SQL)

데이터베이스 이관(마이그레이션)할 때 정상적으로 이관 되었는지 확인 할 때 사용하는 쿼리를 작성했습니다.

운영하는 DB에 따라서 다를 수 있지만, 기본적으로 아래와 같습니다.

운영서버 MINUS 신규운영서버 UNION ALL 신규운영서버 MINUS 운영서버

대부분 운영서버에서 신규운영서버로 이관하게 되지만, 경우에 따라서 양쪽 다 비교해야 하는 경우가 생깁니다. 쿼리를 수행해서 타 시스템의 데이터를 한 번에 비교해 볼 수 있습니다.

대용량 데이터의 경우는 특정 일자 패턴을 잡아서 전일자부터 현재까지만 비교할 수 있도록 개선하였습니다.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67

SELECT    A.TABLE_NAME          ,'SELECT ''NEW_DATA'''           || ' AS DATA_TP, A.*  FROM ('           || A.NEW_DB_SQL           || ' MINUS '           || ORG_DB_SQL           || ') A UNION ALL SELECT ''ORG_DATA'''           || ' AS DATA_TP, B.*  FROM ('           || A.ORG_DB_SQL           || ' MINUS '           || NEW_DB_SQL           || ') B ;              ' FROM      (SELECT    XXX.TABLE_NAME                     ,XXX.D_GB                     ,UPPER('select ' || XXX.ALL_COLUMN_NAME || ' from ' || XXX.TABLE_NAME || XXX.WHERE_STR) NEW_DB_SQL                     ,UPPER('select ' || XXX.ALL_COLUMN_NAME || ' from EXT.' || XXX.TABLE_NAME || '@DBDATA' || XXX.WHERE_STR) ORG_DB_SQL            FROM      (SELECT    XX.TABLE_NAME                                ,CASE XX.D_GB                                   WHEN 'S' THEN ' where std_dt >= to_char(sysdate -1, ''YYYYMMDD'')'                                   WHEN 'U' THEN ' where update_dt >= to_char(sysdate -1, ''YYYYMMDD'')'                                   WHEN 'D' THEN ' where trd_dt >= to_char(sysdate -1, ''YYYYMMDD'')'                                   WHEN 'Y' THEN ' where ymd >= to_char(sysdate -1, ''YYYYMMDD'')'                                 END                                   WHERE_STR /* 대용량 데이터의 경우 1일씩 비교한다. */                                ,XX.D_GB                                ,XX.ALL_COLUMN_NAME                       FROM      (SELECT    X.TABLE_NAME                                           ,(SELECT    DECODE(MIN(U.COLUMN_NAME)                                                             ,'UPDATE_DT', 'U'                                                             ,'STD_DT', 'S'                                                             ,'TRD_DT', 'D'                                                             ,'YMD', 'Y'                                                             ,'M'                                                             )                                             FROM      USER_CONSTRAINTS S                                                      ,USER_CONS_COLUMNS U                                             WHERE     S.TABLE_NAME = X.TABLE_NAME                                             AND       S.CONSTRAINT_TYPE = 'P'                                             AND       U.CONSTRAINT_NAME = S.CONSTRAINT_NAME                                             AND       U.COLUMN_NAME IN ('UPDATE_DT', 'STD_DT', 'TRD_DT', 'YMD')                                             AND       ROWNUM = 1)                                              D_GB                                           ,X.ALL_COLUMN_NAME                                  FROM      (SELECT    A.TABLE_NAME, MAX(TO_CHAR(A.ALL_COLUMN_NAME)) ALL_COLUMN_NAME                                             FROM      (SELECT    A.TABLE_NAME                                                                 ,WM_CONCAT(A.COLUMN_NAME) OVER (PARTITION BY A.TABLE_NAME ORDER BY A.COLUMN_ID)                                                                    ALL_COLUMN_NAME                                                        FROM      COLS A                                                        WHERE     1 = 1                                                        AND       (A.COLUMN_NAME NOT IN ('ALTR_DTTM'                                                                                        ,'ALTR_USER_ID'                                                                                        ,'REG_USER_ID'                                                                                        ,'REG_DTTM'                                                                                        ,'REG_DATE'                                                                                        ,'DESCRIPT'                                                                                        ,'NAME')  -- 컬럼 비교 대상에서 제외                                                        AND        A.COLUMN_NAME NOT LIKE '%_NM%'                                                        AND        A.COLUMN_NAME NOT LIKE '%_DESC%'                                                        AND        A.COLUMN_NAME NOT LIKE '%DESCRIPTION'                                                        AND        A.COLUMN_NAME NOT LIKE '%DESCR'                                                        AND        A.COLUMN_NAME NOT LIKE '%MSG_COMMENT') -- 컬럼에서 비교 대상을 제외                                                        AND       A.TABLE_NAME IN ('TAB_DATA'                                                                                  ,'TAB_DATA_LIST'                                                                                  ,'TAB_CODE') -- 테이블 검색                                                                                   ) A                                             GROUP BY  A.TABLE_NAME) X) XX) XXX) A;