[오라클]audit(시스템) 컬럼 존재, 순서 확인 쿼리

오라클 audit(시스템) 컬럼 체크

프로젝트에서는 테이블을 생성할때 필수적으로 audit 컬럼이 들어가야한다. 

sql 작성가이드에서도 명시되어 있을만큼 db 관리에 있어 중요하기 때문이다. 

 

AUDIT 컬럼 : 작성자ID,작성일,수정자ID,수정일

 

솔루션 때문에 어쩔 수 없이 비표준 모델로 신청된 테이블이 아닌 이상 무조건 들어가야하고, 그만큼 중요하다. 

 

문제는... 모델 변경 과정에서 추가되는 컬럼, 그때 반영시 틀어지는 경우가 있는데 

그걸 찾아내서 변경 요청해야한다. 

 

자... 나에게 부여된 mission.

AUDIT 컬럼이 위배된 테이블을 찾아라 !! 

 

1. 어떤 테이블을 사용할까?-  DBA_TAB_COLS

 

DBA_TAB_COLS 테이블을 사용하면 컬럼ID, 테이블명, 컬럼명을 확보할 수 있다. 

AUDIT 컬럼은 테이블의 마지막 네개의 컬럼에 위치해야하므로 우선 각 테이블의 마지막 4개의 컬럼을 뽑아야한다. 

SELECT TABLE_NAME
     , ROW_NUMBER() OVER (PARTITION BY TABLE_NAME ORDER BY COLUMN_ID DESC) AS "COL_ID"
     , COLUMN_NAME
  FROM DBA_TAB_COLS
 WHERE OWNER='OWNER';

위 쿼리에서 ROWNUM 활용하여 컬럼 아이디를 내림차순으로 정렬하여 컬럼아이디를 다시 생성해줬다.

 

2. 어떻게 조인해야할까?

 

내가 원하는건 마지막 네 컬럼이 순서대로 잘 들어가있는지 확인하는것이다. 따라서 다른 컬럼은 필요없고 마지막 네 컬럼만 LISTAGG를 사용해서 합치고, 그 다음 비교하면 될것같다 !

SELECT A.TABLE_NAME
     , LISTAGG(A.COLUMN_NAME,',') WITHIN GROUP(ORDER BY A.COLUMN_ID DESC) AS "COL_CHECK"
  FROM DBA_TAB_COLS A
     , (SELECT TABLE_NAME
             , ROW_NUMBER() OVER (PARTITION BY TABLE_NAME ORDER BY COLUMN_ID DESC) AS "COL_ID"
             , COLUMN_NAME
          FROM DBA_TAB_COLS
         WHERE OWNER = 'OWNER') B
 WHERE A.OWNER = 'OWNER'
   AND B.TABLE_NAME = A.TABLE_NAME
   AND B.COLUMN_NAME = A.COLUMN_NAME
   AND B.COL_ID <5
 GROUP BY A.TABLE_NAME ;

 

위 쿼리는 테이블 소유자가 OWNER 인 테이블의 마지막 네 컬럼을 테이블별로 CONCAT 하여 나타낸 쿼리이다. 

이제 내가 원하는 문자열과 동일하지 않은 (즉, 순서가 잘못되거나 없는 컬럼을) 조회해보자. 

SELECT *
  FROM (
SELECT A.TABLE_NAME
     , LISTAGG(A.COLUMN_NAME,',') WITHIN GROUP(ORDER BY A.COLUMN_ID DESC) AS "COL_CHECK"
  FROM DBA_TAB_COLS A
     , (SELECT TABLE_NAME
             , ROW_NUMBER() OVER (PARTITION BY TABLE_NAME ORDER BY COLUMN_ID DESC) AS "COL_ID"
             , COLUMN_NAME
          FROM DBA_TAB_COLS
         WHERE OWNER = 'OWNER') B
 WHERE A.OWNER = 'OWNER'
   AND B.TABLE_NAME = A.TABLE_NAME
   AND B.COLUMN_NAME = A.COLUMN_NAME
   AND B.COL_ID <5
 GROUP BY A.TABLE_NAME) C
 WHERE C.COL_CHECK <> 'MDFCN_DT,MDFR_ID,REG_DT,RGTR_ID'
   AND NOT REGEXP_LIKE(C.TABLE_NAME,'[0-9]$') ;

 

그럼 조건에 맞지 않은 테이블만 찾을 수 있다! 

촤라 ~~~