오라클 오브젝트 비교 검증쿼리 만들기

오라클 오브젝트 비교하는 이유

오라클 오브젝트를 비교하는 이유는 오라클 데이터베이스를 사용하다 보면 운영, 개발을 나눠서 사용하는 경우도 있고 개발자들이 운영과 동일한 환경을 만들어 놓고 테스트와 검증을 하기 위한 스테이징 서버를 구성하는 경우도 있습니다. 이렇게 여러 가지 환경이 주어질 때 동일한 데이터베이스 환경을 구축하기 위해 오라클 오브젝트들을 이관하게 되고 각 오브젝트별로 환경이 동일한지 체크를 하기 위한 오브젝트 비교가 필요할 수 있습니다.

오라클 오브젝트 종류와 비교 대상

오라클 오브젝트는 행과 열로 구성된 데이터의 집합을 저장하는 테이블(Table), 하나 이상의 테이블에서 데이터를 조합해 가상의 테이블로 구성하는 뷰(View), 빠른 데이터 검색을 위해 사용되는 인덱스(Index), 일련번호를 자동으로 저장하는 시퀀스(Sequence), 절차적 언어로 단계별 실행을 할 수 있는 프로시저(Procedure), 특정 계산이나 변환 작업을 수행하기 위한 함수(Function), 프로시저와 함수를 논리적으로 그룹화할 수 있는 패키지(Package), 특정 이벤트(테이블의 Insert, Update, Delete 등)를 감지해서 자동으로 실행되는 코드 블록인 트리거(Trigger) 등이 있습니다. 패키지는 선언부(Spac)와 몸체(Body)가 각각 별도의 오브젝트로 되어 있습니다. 이외에도 클러스터나, 사용자 정의 타입 등 많은 오브젝트가 존재하지만 기본적으로 많이 사용하는 오브젝트 몇 가지만 추려서 검증하는 쿼리를 작성해 보려고 합니다.

오라클 오브젝트 비교 검증쿼리

운영(REAL) 데이터베이스를 가지고 개발(DEV) 데이터베이스를 구축한다고 가정해 보겠습니다. 개발 데이터베이스를 구축하고 운영 데이터베이스와 개발 데이터베이스의 오브젝트를 비교하려고 합니다. 반대의 상황도 적용 가능합니다.

비교 환경 구축하기

1. 개발(DEV) 데이터베이스에 임시 테이블 생성하기

-- DBA_OBJECTS 임시 테이블 생성
CREATE TABLE REAL_DBA_OBJECTS
SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_TYPE
  FROM DBA_OBJECTS
 WHERE 1 = 2;

-- DBA_TAB_COLUMNS 임시 테이블 생성
CREATE TABLE REAL_DBA_TAB_COLUMNS
SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, NULLABLE
  FROM DBA_TAB_COLUMNS
 WHERE 1 = 2;
 
 -- DBA_SOURCE 임시 테이블 생성
CREATE TABLE REAL_DBA_SOURCE
SELECT OWNER, NAME, TYLE, LINE, TEXT
  FROM DBA_SOURCE
 WHERE 1 = 2;
 
 -- DBA_CONSTRAINTS 임시 테이블 생성
 CREATE TABLE REAL_DBA_CONSTRAINTS (
      OWNER VARCHAR2 (128 Byte)
    , TABLE_NAME VARCHAR2 (128 Byte)
    , CONSTRAINT_TYPE VARCHAR2 (1 Byte)
    , SEARCH_CONDITION_VC VARCHAR2 (4000 Byte)
    , INDEX_OWNER VARCHAR2 (128 Byte)
    , INDEX_NAME VARCHAR2 (128 Byte)
    , IDX_COLUMN_NAME VARCHAR2 (1000 Byte)
);

2-1. 운영(REAL) DB의 DBA_OBJECTS 테이블 스크립트를 개발(DEV) DB에 복사하기

/*-----------------------------------
* 운영(REAL) DB에서 실행
-----------------------------------*/
-- 저장할 공간을 만듭니다.
Spool on ./REAL_DBA_OBJECTS.sql;

-- 실행쿼리를 파일에 기록합니다.
-- 필요한 대상만 조회합니다.
SELECT 'INSERT INTO REAL_DBA_OBJECTS (OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_TYPE) ' ||
       'VALUES (''' || A.OWNER || ''',''' || A.OBJECT_NAME ||''',''' || A.SUBOBJECT_NAME ||''',''' || A.OBJECT_TYPE ||''')' 
  FROM DBA_OBJECTS A
 --WHERE OWNER IN ('HR', 'DEPT');

-- Spool의 기록을 종료합니다.
Spool off;

/*-----------------------------------
* 개발(DEV) DB에서 실행
-----------------------------------*/
-- 파일을 실행합니다.
@REAL_DBA_OBJECTS.sql;

2-2. 운영(REAL) DB의 DBA_TAB_COLUMNS 테이블 스크립트를 개발(DEV) DB에 복사하기

/*-----------------------------------
* 운영(REAL) DB에서 실행
-----------------------------------*/
-- 저장할 공간을 만듭니다.
Spool on ./REAL_DBA_TAB_COLUMNS.sql;

-- 실행쿼리를 파일에 기록합니다.
-- 필요한 대상만 조회합니다.
SELECT 'INSERT INTO REAL_DBA_TAB_COLUMNS (OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_TYPE) ' ||
       'VALUES (''' || A.OWNER || ''',''' || A.TABLE_NAME ||''',''' || A.COLUMN_NAME ||''',''' || A.DATA_TYPE ||''',''' || A.DATA_LENGTH ||''',''' || A.DATA_PRECISION ||''',''' || A.NULLABLE ||''')' 
  FROM DBA_TAB_COLUMNS A
 WHERE OWNER IN ('HR', 'DEPT');

-- Spool의 기록을 종료합니다.
Spool off;

/*-----------------------------------
* 개발(DEV) DB에서 실행
-----------------------------------*/
-- 파일을 실행합니다.
@REAL_DBA_TAB_COLUMNS.sql;

2-3. 운영(REAL) DB의 DBA_SOURCE 테이블 스크립트를 개발(DEV) DB에 복사하기

/*-----------------------------------
* 운영(REAL) DB에서 실행
-----------------------------------*/
-- 저장할 공간을 만듭니다.
Spool on ./REAL_DBA_SOURCE.sql;

-- 실행쿼리를 파일에 기록합니다.
-- 필요한 대상만 조회합니다.
SELECT 'INSERT INTO REAL_DBA_SOURCE (OWNER, NAME, TYPE, LINE, TEXT) ' ||
       'VALUES (''' || A.OWNER || ''',''' || A.NAME ||''',''' || A.TYPE ||''',''' || A.LINE ||''',''' || A.TEXT ||''')' 
  FROM DBA_SOURCE A
 WHERE OWNER IN ('HR', 'DEPT');

-- Spool의 기록을 종료합니다.
Spool off;

/*-----------------------------------
* 개발(DEV) DB에서 실행
-----------------------------------*/
-- 파일을 실행합니다.
@REAL_DBA_SOURCE.sql;

2-4. 운영(REAL) DB의 DBA_CONSTRAINTS 테이블 스크립트를 개발(DEV) DB에 복사하기

/*-----------------------------------
* 운영(REAL) DB에서 실행
-----------------------------------*/
-- 저장할 공간을 만듭니다.
Spool on ./REAL_DBA_CONSTRAINTS.sql;

-- 실행쿼리를 파일에 기록합니다.
-- 필요한 대상만 조회합니다.
SELECT 'INSERT INTO REAL_DBA_CONSTRAINTS (OWNER, TABLE_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION_VC, INDEX_OWNER, INDEX_NAME) ' ||
       'VALUES (''' || A.OWNER || ''',''' || A.TABLE_NAME ||''',''' || 
       A.CONSTRAINT_TYPE ||''',''' || A.SEARCH_CONDITION_VC ||''',''' || 
       A.INDEX_OWNER ||''',''' || A.INDEX_NAME ||''',''' || 
       (SELECT LISTAGG(COLUMN_NAME,',') WITHIN GROUP (ORDER BY COLUMN_POSITION)
          FROM ALL_IND_COLUMNS
         WHERE INDEX_OWNER = A.INDEX_OWNER
           AND INDEX_NAME = A.INDEX_NAME
           AND TABLE_NAME = A.TABLE_NAME) ||''')' 
  FROM DBA_CONSTRAINTS A
 WHERE OWNER IN ('HR', 'DEPT');

-- Spool의 기록을 종료합니다.
Spool off;

/*-----------------------------------
* 개발(DEV) DB에서 실행
-----------------------------------*/
-- 파일을 실행합니다.
@REAL_DBA_CONSTRAINTS.sql;

여기까지 문제없이 잘 진행되셨으면 이제 쿼리만 수행하면 오브젝트 비교를 진행할 수 있습니다.

비교쿼리실행

1. 대상별 확인 - OBJECT_TYPE만 동일하게 변경해서 조회하시면 됩니다.

/*--------------
* 건수 비교
--------------- */
WITH TOBE AS (
    SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_TYPE
      FROM DBA_OBJECTS
     WHERE OWNER IN ('HR', 'DEPT')
       AND OBJECT_TYPE = 'TABLE'     /* 테이블 */
       -- AND OBJECT_TYPE = 'INDEX'  /* 인덱스 */
       -- AND OBJECT_TYPE = 'SEQUENCE'  /* 시퀀스 */
       -- AND OBJECT_TYPE = 'PROCEDURE'  /* 프로시저 */
       -- AND OBJECT_TYPE = 'PACKAGE'  /* 패키지 */
)
, ASIS AS (
    SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_TYPE
      FROM REAL_DBA_OBJECTS
     WHERE OWNER IN ('HR', 'DEPT')
       AND OBJECT_TYPE = 'TABLE'     /* 테이블 */
       -- AND OBJECT_TYPE = 'INDEX'  /* 인덱스 */
       -- AND OBJECT_TYPE = 'SEQUENCE'  /* 시퀀스 */
       -- AND OBJECT_TYPE = 'PROCEDURE'  /* 프로시저 */
       -- AND OBJECT_TYPE = 'PACKAGE'  /* 패키지 */
)
SELECT (SELECT COUNT(*) FROM ASIS) ASIS
     , (SELECT COUNT(*) FROM TOBE) TOBE
  FROM DUAL;
  
/*--------------------------------------------------------------------
* 구조 비교 (OBJECT NAME이 NULL이면 OBJECT가 다르다고 판별하시면 됩니다.)
--------------------------------------------------------------------- */
WITH TOBE AS (
    SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_TYPE
      FROM DBA_OBJECTS
     WHERE OWNER IN ('HR', 'DEPT')
        AND OBJECT_TYPE = 'TABLE'     /* 테이블 */
       -- AND OBJECT_TYPE = 'INDEX'  /* 인덱스 */
       -- AND OBJECT_TYPE = 'SEQUENCE'  /* 시퀀스 */
       -- AND OBJECT_TYPE = 'PROCEDURE'  /* 프로시저 */
       -- AND OBJECT_TYPE = 'PACKAGE'  /* 패키지 */
)
, ASIS AS (
    SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_TYPE
      FROM REAL_DBA_OBJECTS
     WHERE OWNER IN ('HR', 'DEPT')
        AND OBJECT_TYPE = 'TABLE'     /* 테이블 */
       -- AND OBJECT_TYPE = 'INDEX'  /* 인덱스 */
       -- AND OBJECT_TYPE = 'SEQUENCE'  /* 시퀀스 */
       -- AND OBJECT_TYPE = 'PROCEDURE'  /* 프로시저 */
       -- AND OBJECT_TYPE = 'PACKAGE'  /* 패키지 */
)
SELECT A.*, B.*
  FROM ASIS A
       FULL OUTER JOIN  TOBE B
    ON A.OWNER = B.OWNER
   AND A.OBJECT_NAME = B.OBJECT_NAME
 WHERE A.OBJECT_NAME IS NULL
   OR B.OBJECT_NAME IS NULL;
   
/*--------------------------------------------------------------------
* 인덱스 컬럼 비교 (TABLE NAME이 NULL이면 OBJECT가 다르다고 판별하시면 됩니다.)
--------------------------------------------------------------------- */
WITH TOBE AS (
    SELECT OWNER, TABLE_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION_VC, INDEX_OWNER, INDEX_NAME
         , (SELECT LISTAGG(COLUMN_NAME,',') WITHIN GROUP (ORDER BY COLUMN_POSITION)
              FROM ALL_IND_COLUMNS
             WHERE INDEX_OWNER = X.INDEX_OWNER
               AND INDEX_NAME = X.INDEX_NAME
               AND TABLE_NAME = X.TABLE_NAME) IDX_COLUMN_NAME
      FROM DBA_CONSTRAINTS X
     WHERE OWNER IN ('HR', 'DEPT')
       AND CONSTRAINT_NAME NOT LIKE 'BIN%'
)
, ASIS AS (
    SELECT OWNER, TABLE_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION_VC, INDEX_OWNER, INDEX_NAME, IDX_COLUMN_NAME
     FROM REAL_DBA_CONSTRAINTS
)
SELECT A.*, B.*
  FROM ASIS A
       FULL OUTER JOIN  TOBE B
    ON A.OWNER = B.OWNER
   AND A.TABLE_NAME = B.TABLE_NAME
   AND A.CONSTRAINT_TYPE = B.CONSTRAINT_TYPE
   AND NVL(A.SEARCH_CONDITION_VC, 'X') = NVL(B.SEARCH_CONDITION_VC, 'X')
   AND NVL(A.INDEX_OWNER, 'X') = NVL(B.INDEX_OWNER, 'X')
   AND NVL(A.INDEX_NAME, 'X') = NVL(B.INDEX_NAME, 'X')
   AND NVL(A.IDX_COLUMN_NAME, 'X') = NVL(B.IDX_COLUMN_NAME, 'X')
 WHERE A.TABLE_NAME IS NULL
    OR B.TABLE_NAME IS NULL;

2. 테이블 칼럼 확인

-- 개발 DB와 다른 컬럼을 찾습니다.
SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, NULLABLE
  FROM DBA_TAB_COLUMNS
 WHERE OWNER IN ('HR', 'DEPT')
   AND TABLE_NAME NOT LIKE '%BIN$%'
MINUS
SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, NULLABLE
  FROM REAL_DBA_TAB_COLUMNS
   AND TABLE_NAME NOT LIKE '%BIN$%';

-- 운영 DB와 다른 컬럼을 찾습니다.
SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, NULLABLE
  FROM REAL_DBA_TAB_COLUMNS 
   AND TABLE_NAME NOT LIKE '%BIN$%'
  MINUS  
SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, NULLABLE
  FROM DBA_TAB_COLUMNS
 WHERE OWNER IN ('HR', 'DEPT')
   AND TABLE_NAME NOT LIKE '%BIN$%';

3. 파티션 테이블 확인

-- 운영DB와 다른 파티션테이블 정보 입니다.
SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_TYPE
  FROM REAL_DBA_OBJECTS
 WHERE OWNER IN ('HR', 'DEPT')
   AND OBJECT_TYPE = 'TABLE PARTITION'
MINUS
SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_TYPE
  FROM DBA_OBJECTS
 WHERE OWNER IN ('HR', 'DEPT')
   AND OBJECT_TYPE = 'TABLE PARTITION';

-- 개발DB와 다른 파티션테이블 정보 입니다.
SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_TYPE
  FROM DBA_OBJECTS
 WHERE OWNER IN ('HR', 'DEPT')
   AND OBJECT_TYPE = 'TABLE PARTITION'
MINUS
SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_TYPE
  FROM REAL_DBA_OBJECTS
 WHERE OWNER IN ('HR', 'DEPT')
   AND OBJECT_TYPE = 'TABLE PARTITION';

4. PROCEDURE, FUNCTION, PACKAGE 등 세부 텍스트 정보 비교

/*--------------------------------------------------------------------
* 세부텍스트 비교 ( NAME이 NULL이면 OBJECT가 다르다고 판별하시면 됩니다.)
--------------------------------------------------------------------- */
WITH TOBE AS (
    SELECT OWNER, NAME, TYPE, LINE, TEXT
      FROM DBA_SOURCE
     WHERE OWNER IN ('HR', 'DEPT')
)
, ASIS AS (
    SELECT OWNER, NAME, TYPE, LINE, TEXT
      FROM REAL_DBA_SOURCE
     WHERE OWNER IN ('HR', 'DEPT')
)
SELECT A.*, B.*
  FROM ASIS A
       FULL OUTER JOIN  TOBE B
    ON A.OWNER = B.OWNER
   AND A.NAME = B.NAME
   AND A.TYPE = B.TYPE
   AND A.LINE = B.LINE
   AND A.TEXT = B.TEXT
 WHERE A.NAME IS NULL
    OR B.NAME IS NULL;

이와 같이 동일한 DB에 오브젝트 정보를 복사해 두고 쿼리로 비교해서 구조가 다른 케이스를 찾아봤습니다. 다소 내용이 어려울 수 있지만 오브젝트 비교를 어떻게 해야 할지 막막하신 분들에게 꼭 도움이 되었으면 좋겠습니다. 긴 포스팅 끝까지 읽어주셔서 감사합니다.