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

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

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

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

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

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

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;