데이터베이스 비교
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 테이블 비교[편집]
SELECT OWNER,OBJECT_NAME,'Y' OBJECT_TYPE,'Y' EXE_FLAG
FROM (
SELECT OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS
FROM DBA_OBJECTS@DL_xxx_DEV_xxx_DBA
WHERE REGEXP_LIKE (OWNER,'^xxx|^OBT|^ERPAPP|^WEB|^ETAX|^EDI|^BIZWMSG')
MINUS
SELECT OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS
FROM DBA_OBJECTS
WHERE REGEXP_LIKE (OWNER,'^xxx|^OBT|^ERPAPP|^WEB|^ETAX|^EDI|^BIZWMSG')
) A
WHERE OBJECT_TYPE = 'TABLE'
AND REGEXP_LIKE (OWNER,'^xxx$|^OBT$|^WEB$|^BIZWMSG$|^ETAX$|^EDI$')
AND NOT REGEXP_LIKE(OBJECT_NAME ,'^SYS_|^BIN|^XTB_|_OLD$|\_$')
AND NOT EXISTS (SELECT 1 FROM TB_MGR_MIG_TASK X WHERE X.TABLE_NAME = A.OBJECT_NAME)
ORDER BY 1,2
;2 시노님 비교[편집]
SELECT 'CREATE SYNONYM '||OWNER||'.'||SYNONYM_NAME||' FOR '||TABLE_OWNER||'.'||TABLE_NAME||';' DDL
, A.OWNER
, A.SYNONYM_NAME
, A.TABLE_OWNER
, A.TABLE_NAME
FROM (
-- 개발
SELECT OWNER,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME
FROM DBA_SYNONYMS@DL_xxx_DEV_xxx_DBA
WHERE REGEXP_LIKE (OWNER,'^xxx|^OBT|^ERPAPP|^WEB|^ETAX|^EDI|^BIZWMSG')
MINUS
-- 통테
SELECT OWNER,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME
FROM DBA_SYNONYMS
WHERE REGEXP_LIKE (OWNER,'^xxx|^OBT|^ERPAPP|^WEB|^ETAX|^EDI|^BIZWMSG')
) A
-- WHERE A.SYNONYM_NAME <> A.TABLE_NAME
-- AND REGEXP_LIKE (A.OWNER,'^xxx$|^OBT$|^WEB$|^BIZWMSG$|^ETAX$|^EDI$')
-- AND NOT REGEXP_LIKE(A.OBJECT_NAME ,'^SYS_|^BIN|^XTB_|_OLD$|\_$')
-- AND NOT EXISTS (SELECT 1 FROM TB_MGR_MIG_TASK X WHERE X.TABLE_NAME = A.OBJECT_NAME)
ORDER BY 1,2
;3 INDEX명 비교[편집]
SELECT OBJECT_NAME
FROM (
SELECT OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS
FROM DBA_OBJECTS@DL_xxx_DEV_xxx_DBA
WHERE REGEXP_LIKE (OWNER,'^xxx|^OBT|^ERPAPP|^WEB|^ETAX|^EDI|^BIZWMSG')
MINUS
SELECT OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS
FROM DBA_OBJECTS
WHERE REGEXP_LIKE (OWNER,'^xxx|^OBT|^ERPAPP|^WEB|^ETAX|^EDI|^BIZWMSG')
) A
WHERE OBJECT_TYPE = 'INDEX'
AND REGEXP_LIKE (OWNER,'^xxx$|^OBT$|^WEB$|^BIZWMSG$|^ETAX$|^EDI$')
AND NOT REGEXP_LIKE(OBJECT_NAME ,'^SYS_|^BIN|^XTB_|_OLD$|\_$')
-- AND NOT EXISTS (SELECT 1 FROM TB_MGR_MIG_TASK X WHERE X.TABLE_NAME = A.OBJECT_NAME)
ORDER BY 1
;4 컬럼 비교[편집]
SELECT DISTINCT 'CREATE TABLE MIG_BACKUP.'||TABLE_NAME||' AS SELECT * FROM '||OWNER||'.'||TABLE_NAME||';' DDL
FROM (
SELECT OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE
FROM DBA_TAB_COLS@DL_xxx_DEV_xxx_DBA
WHERE REGEXP_LIKE (OWNER,'^xxx|^OBT|^ERPAPP|^WEB|^ETAX|^EDI|^BIZWMSG')
MINUS
SELECT OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE
FROM DBA_TAB_COLS
WHERE REGEXP_LIKE (OWNER,'^xxx|^OBT|^ERPAPP|^WEB|^ETAX|^EDI|^BIZWMSG')
) A
WHERE 1=1
--- AND OBJECT_TYPE = 'INDEX'
AND REGEXP_LIKE (OWNER,'^xxx$|^OBT$|^WEB$|^BIZWMSG$|^ETAX$|^EDI$')
AND NOT REGEXP_LIKE(TABLE_NAME ,'^SYS_|^BIN|^XTB_|_OLD$|\_$')
-- AND NOT EXISTS (SELECT 1 FROM TB_MGR_MIG_TASK X WHERE X.TABLE_NAME = A.OBJECT_NAME)
ORDER BY 1 --,2
;5 프로시져 비교[편집]
SELECT DISTINCT OBJECT_TYPE
FROM DBA_PROCEDURES
;6 오브젝트 전체 비교[편집]
WITH
v_local
AS
(SELECT /*+ rule */
c.owner
, c.table_name
, c.column_id
, c.column_name
, c.nullable
, CASE
WHEN c.data_type IN ('NUMBER')
THEN
c.data_type
|| CASE
WHEN c.data_precision > 0
THEN
'('
|| c.data_precision
|| CASE
WHEN c.data_scale > 0
THEN
',' || c.data_scale
END
|| ')'
END
WHEN c.data_type IN
('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2')
THEN
data_type
|| '('
|| TO_CHAR (c.CHAR_LENGTH)
|| ')'
WHEN c.data_type LIKE 'TIMESTAMP%'
THEN
c.data_type
ELSE
c.data_type
END AS data_type_length
FROM dba_tab_columns c
WHERE c.owner IN ('xxx', 'ERPAPP', 'ETAX', 'OBT')
AND c.table_name NOT LIKE 'BIN$%'),
v_remote
AS
(SELECT /*+ driving_site(c) */
c.owner
, c.table_name
, c.column_id
, c.column_name
, c.nullable
, CASE
WHEN c.data_type IN ('NUMBER')
THEN
c.data_type
|| CASE
WHEN c.data_precision > 0
THEN
'('
|| c.data_precision
|| CASE
WHEN c.data_scale > 0
THEN
',' || c.data_scale
END
|| ')'
END
WHEN c.data_type IN
('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2')
THEN
data_type
|| '('
|| TO_CHAR (c.CHAR_LENGTH)
|| ')'
WHEN c.data_type LIKE 'TIMESTAMP%'
THEN
c.data_type
ELSE
c.data_type
END AS data_type_length
FROM dba_tab_columns@dl_xxx_dev_xxx_dba c
WHERE c.owner IN ('xxx', 'ERPAPP', 'ETAX', 'OBT')
AND c.table_name NOT LIKE 'BIN$%'),
v_difftab
AS
((SELECT a.owner, a.table_name, 'Deleted' AS chk
FROM v_local a
MINUS
SELECT a.owner, a.table_name, 'Deleted' AS chk
FROM v_remote a)
UNION ALL
(SELECT a.owner, a.table_name, 'Added' AS chk
FROM v_remote a
MINUS
SELECT a.owner, a.table_name, 'Added' AS chk
FROM v_local a)),
v_diffcol_local
AS
(SELECT a.*, 'Local' site
FROM v_local a
WHERE (owner, table_name) NOT IN
(SELECT owner, table_name FROM v_difftab)
MINUS
SELECT a.*, 'Local' site
FROM v_remote a
WHERE (owner, table_name) NOT IN
(SELECT owner, table_name FROM v_difftab)),
v_diffcol_remote
AS
(SELECT a.*, 'Remote' site
FROM v_remote a
WHERE (owner, table_name) NOT IN
(SELECT owner, table_name FROM v_difftab)
MINUS
SELECT a.*, 'Remote' site
FROM v_local a
WHERE (owner, table_name) NOT IN
(SELECT owner, table_name FROM v_difftab)),
v_diff_all
AS
(SELECT 1 cn
, owner
, table_name
, NULL col_id
, NULL column_name
, chk AS diff
, NULL data_type_length
, NULL nullable
, NULL column_id
, NULL X
, NULL type_local
, NULL null_local
, NULL id_local
, NULL type_remote
, NULL null_remote
, NULL id_remote
FROM v_difftab
UNION ALL
SELECT 2 cn
, NVL (a.owner, b.owner) owner
, NVL (a.table_name, b.table_name) table_name
, NVL (b.column_id, a.column_id) col_id -- remote first
, NVL (a.column_name, b.column_name) column_name
-----------------------------------------------------------------------------------------
, CASE
WHEN a.column_name IS NULL
THEN
'[Col-New]'
WHEN b.column_name IS NULL
THEN
'[Col-Del]'
ELSE
CASE
WHEN a.data_type_length <>
b.data_type_length
THEN
'[Type]'
END
|| CASE
WHEN a.nullable <> b.nullable
THEN
'[Null]'
END
|| CASE
WHEN a.column_id <> b.column_id
THEN
'[Order]'
END
END diff
, CASE
WHEN a.column_name = b.column_name
AND a.data_type_length <> b.data_type_length
THEN
a.data_type_length || '▶' || b.data_type_length
END data_type_length
, CASE
WHEN a.column_name = b.column_name
AND a.nullable <> b.nullable
THEN
a.nullable || '▶' || b.nullable
END nullable
, CASE
WHEN a.column_name = b.column_name
AND a.column_id <> b.column_id
THEN
a.column_id || '▶' || b.column_id
END column_id
, '◆' X
, a.data_type_length type_local
, a.nullable null_local
, a.column_id id_local
, b.data_type_length type_remote
, b.nullable null_remote
, b.column_id id_remote
FROM v_diffcol_local a
FULL JOIN v_diffcol_remote b
ON ( b.owner = a.owner
AND b.table_name = a.table_name
AND b.column_name = a.column_name))
SELECT "CN"
, "OWNER"
, "TABLE_NAME"
, "COL_ID"
, "COLUMN_NAME"
, "DIFF"
, "DATA_TYPE_LENGTH"
, "NULLABLE"
, "COLUMN_ID"
, "X"
, "TYPE_LOCAL"
, "NULL_LOCAL"
, "ID_LOCAL"
, "TYPE_REMOTE"
, "NULL_REMOTE"
, "ID_REMOTE"
FROM v_diff_all
WHERE diff <> '[Order]'
ORDER BY cn, owner, table_name, col_id;