오라클 테이블 스키마 비교 - olakeul teibeul seukima bigyo

개발하는 동안에 두 테이블간의 테이블 구조를 비교할 경우가 간혹 생깁니다.

두 테이블의 DDL문을 뽑아서 눈으로 직접 비교할 수도 있지만, 아래의 쿼리를 사용하면 편하게 비교할 수 있습니다.

두 개의 테이블에서 컬럼명, 데이터 타입, 널 여부, 컬럼 순서 등의 정보들을 각각 비교하여 서로 틀린 점이 있는 컬럼 정보를 확인하기 위해 사용하는 쿼리입니다. FULL OUTER JOIN 을 사용하여 비교하므로 두 개의 테이블이 모두 기준이 됩니다.

테이블명 뒤에 디비 링크 정보를 추가하여 다른 DB 서버의 테이블과도 비교가 가능합니다.

아래 샘플은 USER_AAA.TABLE_AAA, USER_BBB.TABLE_BBB 두 개의 테이블을 비교하고 있습니다.

WITH

A AS (

SELECT COLUMN_NAME AS X_COLUMN_NAME, DATA_TYPE AS X_DATA_TYPE

, DATA_LENGTH AS X_DATA_LENGTH, DATA_PRECISION AS X_DATA_PRECISION

, DATA_SCALE AS X_DATA_SCALE, NULLABLE AS X_NULLABLE

, COLUMN_ID AS X_COLUMN_ID

FROM ALL_TAB_COLUMNS

WHERE OWNER = 'USER_AAA'

AND TABLE_NAME = 'TABLE_AAA'

),

B AS (

SELECT COLUMN_NAME AS Y_COLUMN_NAME, DATA_TYPE AS Y_DATA_TYPE

, DATA_LENGTH AS Y_DATA_LENGTH, DATA_PRECISION AS Y_DATA_PRECISION

, DATA_SCALE AS Y_DATA_SCALE, NULLABLE AS Y_NULLABLE

, COLUMN_ID AS Y_COLUMN_ID

FROM ALL_TAB_COLUMNS

WHERE OWNER = 'USER_BBB'

AND TABLE_NAME = 'TABLE_BBB'

)

SELECT CASE

WHEN X_COLUMN_NAME <> Y_COLUMN_NAME        THEN 'COLUMN_NAME'

WHEN X_DATA_TYPE <> Y_DATA_TYPE            THEN 'DATA_TYPE'

WHEN X_DATA_LENGTH <> Y_DATA_LENGTH        THEN 'DATA_LENGTH'

WHEN X_DATA_PRECISION <> Y_DATA_PRECISION  THEN 'DATA_PRECISION'

WHEN X_DATA_SCALE <> Y_DATA_SCALE          THEN 'DATA_SCALE'

WHEN X_NULLABLE <> Y_NULLABLE              THEN 'NULLABLE'

WHEN X_COLUMN_ID <> Y_COLUMN_ID            THEN 'COLUMN_ID'

WHEN X_COLUMN_NAME IS NULL                 THEN 'X_NULL'

WHEN Y_COLUMN_NAME IS NULL                 THEN 'Y_NULL'

END AS DIFF

, M.*

FROM (

SELECT A.*, B.*

FROM A FULL OUTER JOIN B

ON A.X_COLUMN_NAME = B.Y_COLUMN_NAME

) M

WHERE X_COLUMN_NAME <> Y_COLUMN_NAME

OR X_DATA_TYPE <> Y_DATA_TYPE           

OR X_DATA_LENGTH <> Y_DATA_LENGTH       

OR X_DATA_PRECISION <> Y_DATA_PRECISION 

OR X_DATA_SCALE <> Y_DATA_SCALE         

OR X_NULLABLE <> Y_NULLABLE             

OR X_COLUMN_ID <> Y_COLUMN_ID           

OR X_COLUMN_NAME IS NULL                

OR Y_COLUMN_NAME IS NULL                

ORDER BY X_COLUMN_ID

[정정] 서로 다른 스키마에 있는 테이블 비교하는 방법 좀 알려주세요 0 8 5,951

by 박동혁 [2009.07.01 14:50:41]

안녕하세요.

몇년만에 오라클업무를 하게 되었습니다.

오늘 처음으로 받은 업무가...

서로 다른 스키마에 있는 테이블들을 전부 비교하는 것 입니다.

그리고, 그 2개의 스키마(UK1_ID, GARA_ID)의 테이블정의는 똑같게 되어있어 있습니다.

그러나 현재 각각의 사용자에 의해서 데이타가 틀어져 있는 것으로 판단되어서...

양쪽의 스키마의 테이블 값을 체크 하려고 합니다. 혹은 같은지를....

테이블수도 많고...각각 테이블의 컬럼수도 만만치가 않습니다.

그래서, 고수님들의 도움을 받고자 합니다.

오랜만에 오라클로 컴백한 초보오라클유저에게 도움 주시기를 바랍니다.

감사합니다.

p.s. select  * from DICT; 를 사용하면 될꺼라고 하는데...아직 어찌해야 할지 모르겠습니다.

더불어......위의 스키마에 있는 각각의 테이블들의 테이블정의를 비교 하는 방법도 부탁드립니다.

by 마농 [2009.07.01 15:49:55]

SELECT * FROM dba_tab_columns WHERE owner = 'UK1_ID'
MINUS
SELECT * FROM dba_tab_columns WHERE owner = 'GARA_ID'

by 박동혁 [2009.07.01 16:21:07]

마농님!!
답변 감사드립니다.
그런데....dba_tab_columns의 뷰나 테이블이 없다는 메세지가 나옵니다.
일반유저로는 사용 못하는 뷰나 테이블인가요?
혹은 다른 문제가 있는 것인가요?

by 마농 [2009.07.01 16:38:36]

양쪽을 비교하려면 양쪽 정보을 다 볼 수 있어야 하는데요
그러러면 관리자 권한이 있어서 dba_tab_columns 를 볼 수 있어야겠죠.
음.. 그리고 답글을 다는 사이 질문이 바뀌었네요. 스키마 비교에서 데이터 비교로.
데이터 비교는 일일이 테이블별로 minus해서 비교하셔야 합니다.
SELECT * FROM uk1_id.테이블명1 MINUS SELECT * FROM gara_id.테이블명1
UNION ALL
SELECT * FROM gara_id.테이블명1 MINUS SELECT * FROM uk1_id.테이블명1

by 박동혁 [2009.07.01 17:25:19]

마농님, 감사합니다.
---------------------------
SELECT A.TABLE_NAME, A.COLUMN_NAME, A.DATA_TYPE, A.DATA_LENGTH, B.CONSTRAINT_NAME, B.CONSTRAINT_TYPE, B.DELETE_RULE
FROM dba_tab_columns A, ALL_CONSTRAINTS B
WHERE A.OWNER = B.OWNER
AND A.TABLE_NAME = B.TABLE_NAME
AND A.owner = 'GARA_DBA_ID'
MINUS
SELECT A.TABLE_NAME, A.COLUMN_NAME, A.DATA_TYPE, A.DATA_LENGTH, B.CONSTRAINT_NAME, B.CONSTRAINT_TYPE, B.DELETE_RULE
FROM dba_tab_columns A, ALL_CONSTRAINTS B
WHERE A.OWNER = B.OWNER
AND A.TABLE_NAME = B.TABLE_NAME
AND A.owner = 'UK1_DBA_ID'
---------------------------
이렇게 하여서...대략 원하는 정보를 찾았습니다.
그런데...저기에 다른 정보는 더 찾아달라고 해서...현재 진행중입니다.
감을 못 잡고 있었는데....
마농님 덕분에...많은 진척이 있었습니다. 다시 한번 감사드립니다.

by 마농 [2009.07.01 17:36:48]

조인을 잘 하신것인지는 모르겠네요.
불필요한 중복자료가 다수 발생할 것으로 예상됩니다.
테이블간 조인하실때 조인키를 잘 확인하세요.
dba_tab_columns의 키는 컬럼명까지이고 constraint는 컬럼과는 별개의 사항이라 컬럼수만큼 불필요한 조인이 발생되겠네요.

by 박동혁 [2009.07.02 10:34:44]

마농님, 맞습니다.
현재 조인이 잘못 되어있는 것을 확인하고 수정중인데....
dba_tab_columns테이블과 ALL_CONSTRAINTS테이블의 조인 조건을 못 맞추겠습니다.
dba_tab_columns은 테이블명과 컬럼값으로 검색이 가능한데,
ALL_CONSTRAINTS의 경우에는 컬럼값은 관계없이 테이블명이 지정되어 있습니다.
그래서 두 테이블을 따로따로 나누어서 2개의 SQL을 작성해서 진행 중입니다.

그런데...이렇게 해서 원하는 결과를 얻을수 있을지 모르겠습니다.
원하는 결과는 두 스키마에 있는 모든 테이블의 정의는 같다는 전제하에....
차이점을 찾아내서 다시 동기화 시키고자 하는 것입니다.

by 마농 [2009.07.02 10:50:49]

진행하시는 방법이 맞습니다.
각각 따로 따로 서로 다른 부분만 찾아내시면 됩니다.
굳이 조인까지 하실 필요는 없어보입니다.
딕셔너리뷰를 통해 더 확인할 사항은 없는지 살펴보세요.
SELECT * FROM dict;

by 박동혁 [2009.07.02 10:57:19]

마농님이께서 이렇게 진행해도 좋다고 하니...한결 편하게 작업할수 있겠습니다.
그런데 DICT테이블에서는 무엇을 비교해야 할지 테이블을 봐도 잘 모르겠는데...
무엇을 비교해 하는지 조언 부탁드립니다.

다시한번 좋은 답변에 대해서 감사드립니다.