"데이터베이스 비교"의 두 판 사이의 차이
DB CAFE
(새 문서: == 테이블 비교 == SELECT OWNER,OBJECT_NAME,'Y' OBJECT_TYPE,'Y' EXE_FLAG FROM ( SELECT OWNER,OBJECT_NAME,O...) |
(→오브젝트 전체 비교) |
||
89번째 줄: | 89번째 줄: | ||
== 오브젝트 전체 비교 == | == 오브젝트 전체 비교 == | ||
− | + | ||
<source lang=sql> | <source lang=sql> | ||
WITH | WITH |
2019년 6월 21일 (금) 20:44 판
- 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_RTIS_DEV_RTIS_DBA WHERE REGEXP_LIKE (OWNER,'^RTIS|^OBT|^ERPAPP|^WEB|^ETAX|^EDI|^BIZWMSG') MINUS SELECT OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS FROM DBA_OBJECTS WHERE REGEXP_LIKE (OWNER,'^RTIS|^OBT|^ERPAPP|^WEB|^ETAX|^EDI|^BIZWMSG') ) A WHERE OBJECT_TYPE = 'TABLE' AND REGEXP_LIKE (OWNER,'^RTIS$|^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_RTIS_DEV_RTIS_DBA WHERE REGEXP_LIKE (OWNER,'^RTIS|^OBT|^ERPAPP|^WEB|^ETAX|^EDI|^BIZWMSG') MINUS -- 통테 SELECT OWNER,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME FROM DBA_SYNONYMS WHERE REGEXP_LIKE (OWNER,'^RTIS|^OBT|^ERPAPP|^WEB|^ETAX|^EDI|^BIZWMSG') ) A
-- WHERE A.SYNONYM_NAME <> A.TABLE_NAME -- AND REGEXP_LIKE (A.OWNER,'^RTIS$|^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_RTIS_DEV_RTIS_DBA WHERE REGEXP_LIKE (OWNER,'^RTIS|^OBT|^ERPAPP|^WEB|^ETAX|^EDI|^BIZWMSG') MINUS SELECT OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS FROM DBA_OBJECTS WHERE REGEXP_LIKE (OWNER,'^RTIS|^OBT|^ERPAPP|^WEB|^ETAX|^EDI|^BIZWMSG') ) A
WHERE OBJECT_TYPE = 'INDEX'
AND REGEXP_LIKE (OWNER,'^RTIS$|^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
select * from DBA_TAB_COLS@DL_RTIS_DEV_RTIS_DBA
WHERE OWNER ='RTIS'
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_RTIS_DEV_RTIS_DBA WHERE REGEXP_LIKE (OWNER,'^RTIS|^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,'^RTIS|^OBT|^ERPAPP|^WEB|^ETAX|^EDI|^BIZWMSG') ) A
WHERE 1=1 --- AND OBJECT_TYPE = 'INDEX'
AND REGEXP_LIKE (OWNER,'^RTIS$|^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
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 ('RTIS', '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_rtis_dev_rtis_dba c
WHERE c.owner IN ('RTIS', '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;