오브젝트 비교 & 이관
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 로컬/타겟 테이블/컬럼 차이 비교(뷰 생성)[편집]
CREATE OR REPLACE FORCE VIEW V_DIFF_TAB_MIG_PROD
(
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
)
BEQUEATH DEFINER
AS
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'
, 'EDI'
, 'WEB'
, 'AMEX_xxx'
, 'AMEX_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@DB링크 c
WHERE c.owner IN ('xxx'
, 'ERPAPP'
, 'ETAX'
, 'OBT'
, 'EDI'
, 'WEB'
, 'AMEX_xxx'
, 'AMEX_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;2 오브젝트 확인[편집]
SELECT DECODE(USERNAME, 'PUBLIC', '_PUBLIC_', USERNAME) USERNAME,
COUNT(DECODE(O.OBJECT_TYPE, 'TABLE', O.OBJECT_TYPE, '')) TAB,
COUNT(DECODE(O.OBJECT_TYPE, 'TABLE PARTITION', O.OBJECT_TYPE, '')) TAB_P,
COUNT(DECODE(O.OBJECT_TYPE, 'INDEX', O.OBJECT_TYPE, '')) IND,
COUNT(DECODE(O.OBJECT_TYPE, 'INDEX PARTITION', O.OBJECT_TYPE, '')) IND_P,
COUNT(DECODE(O.OBJECT_TYPE, 'SYNONYM', O.OBJECT_TYPE, '')) SYN,
COUNT(DECODE(O.OBJECT_TYPE, 'VIEW', O.OBJECT_TYPE, '')) VEW,
COUNT(DECODE(O.OBJECT_TYPE, 'SEQUENCE', O.OBJECT_TYPE, '')) SEQ,
COUNT(DECODE(O.OBJECT_TYPE, 'PROCEDURE', O.OBJECT_TYPE, '')) PRC,
COUNT(DECODE(O.OBJECT_TYPE, 'FUNCTION', O.OBJECT_TYPE, '')) FNC,
COUNT(DECODE(O.OBJECT_TYPE, 'PACKAGE', O.OBJECT_TYPE, '')) PKG,
COUNT(DECODE(O.OBJECT_TYPE, 'PACKAGE BODY', O.OBJECT_TYPE, '')) PKG_BOBY,
COUNT(DECODE(O.OBJECT_TYPE, 'TRIGGER', O.OBJECT_TYPE, '')) TRG,
COUNT(DECODE(O.OBJECT_TYPE, 'DATABASE LINK', O.OBJECT_TYPE, '')) DBLINK,
COUNT(DECODE(O.OBJECT_TYPE, 'LOB', O.OBJECT_TYPE, '')) LOB,
COUNT(DECODE(O.OBJECT_TYPE, 'MATERIALIZED VIEW', O.OBJECT_TYPE, '')) M_VIW,
COUNT(DECODE(O.OBJECT_TYPE, 'TABLE SUBPARTITION', O.OBJECT_TYPE, '')) TABSBP,
COUNT(DECODE(O.OBJECT_TYPE, 'INDEX SUBPARTITION', O.OBJECT_TYPE, '')) INDSBP,
COUNT(DECODE(O.OBJECT_TYPE, 'LOB PARTITION', O.OBJECT_TYPE, '')) LOBP,
COUNT(DECODE(O.OBJECT_TYPE, 'LOB SUBPARTITION', O.OBJECT_TYPE, '')) LOBSUP
FROM SYS.DBA_OBJECTS O,
(SELECT USERNAME
FROM SYS.DBA_USERS
UNION ALL
SELECT 'PUBLIC'
FROM DUAL)U
WHERE U.USERNAME = O.OWNER (+)
AND USERNAME NOT IN ('SYS',
'SYSTEM',
'DBSNMP',
'WMSYS',
'SYSMAN',
'MDSYS' ,
'EXFSYS',
'XDB',
'ORDSYS',
'TSMSYS',
'OUTLN',
'PAK',
'OLAPSYS',
'ORANGE',
'ORDPLUGINS',
'QUEST',
'SI_INFORMTN_SCHEMA',
'CTXSYS',
'DMSYS',
'ORACLE_OCM',
'SCOTT',
'MAXGAUGE',
'PERFSTAT',
'ANONYMOUS',
'APPQOSSYS',
'MDDATA',
'MGMT_VIEW',
'WKPROXY',
'WKSYS',
'WK_TEST',
'PGUSER')
GROUP BY DECODE(USERNAME, 'PUBLIC', '_PUBLIC_', USERNAME)
ORDER BY USERNAME;3 권한 확인[편집]
SELECT GRANTEE,
OWNER||'.'|| TABLE_NAME ,
PRIVILEGE
FROM DBA_TAB_PRIVS
WHERE (GRANTEE NOT IN ('SYS',
'SYSTEM',
'ORACLE_OCM',
'XS$NULL',
'MDDATA',
'IX',
'DIP',
'OE',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'DBSNMP',
'SYSMAN',
'FLOWS_FILES',
'MDSYS',
'ORDSYS',
'EXFSYS',
'WMSYS',
'APPQOSSYS',
'APEX_030200',
'OWBSYS_AUDIT',
'ORDDATA',
'CTXSYS',
'ANONYMOUS',
'ORDPLUGINS',
'OWBSYS',
'OLAPSYS',
'MGMT_VIEW',
'OUTLN')
AND OWNER NOT IN ('SYS',
'SYSTEM',
'ORACLE_OCM',
'XS$NULL',
'MDDATA',
'IX',
'DIP',
'OE',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'DBSNMP',
'SYSMAN',
'FLOWS_FILES',
'MDSYS',
'ORDSYS',
'EXFSYS',
'WMSYS',
'APPQOSSYS',
'APEX_030200',
'OWBSYS_AUDIT',
'ORDDATA',
'CTXSYS',
'ANONYMOUS',
'ORDPLUGINS',
'OWBSYS',
'OLAPSYS',
'MGMT_VIEW',
'OUTLN') )
AND TABLE_NAME NOT LIKE '%BIN$%';4 INVALID 확인[편집]
SELECT OWNER,
OBJECT_TYPE,
OBJECT_NAME,
STATUS
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID';
== 데이터 건수(Rows) 비교 확인 ==
-- 양쪽 노드에서 수행
<script lang=sql>
CREATE TABLE SYSTEM.TBLCOUNT(TBL_NAME VARCHAR(50), TBL_CNT NUMBER);
DECLARE V_SQL VARCHAR2(1000);
CURSOR CUR1 IS SELECT *
FROM DBA_TABLES
WHERE OWNER IN UPPER( :USERNAME )
ORDER BY OWNER, TABLE_NAME;
BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE SYSTEM.TBLCOUNT';
FOR RECTBL IN CUR1 LOOP V_SQL := 'INSERT INTO SYSTEM.TBLCOUNT SELECT ''' || RECTBL.OWNER||'.'|| RECTBL.TABLE_NAME ||''', COUNT(*) FROM ' || RECTBL.OWNER||'."'|| RECTBL.TABLE_NAME ||'"' ;
DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
END LOOP;
COMMIT;
END;5 시퀀스 변경사항 적용[편집]
SELECT ' DROP SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||';
CREATE SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||'
INCREMENT BY 1
START WITH '||LAST_NUMBER||'
MAXVALUE '||MAX_VALUE||'
MINVALUE '||MIN_VALUE||'
'||DECODE(CYCLE_FLAG,'Y','CYCLE','NOCYCLE')||' CACHE '||CACHE_SIZE||'
NOORDER
NOKEEP
GLOBAL;
SELECT '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||'.NEXTVAL FROM DUAL;
' DDL
-- , 'SELECT '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||'.NEXTVAL FROM DUAL;' SEL_NEXT
-- , 'ALTER SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||' INCREMENT BY 1;'
FROM DBA_SEQUENCES
WHERE SEQUENCE_OWNER LIKE 'MIG%' ;-- 타겟 디비에서 수행
SELECT *
FROM (SELECT TG.TBL_NAME,
SR.TBL_CNT SOURCE_TBLCOUNT ,
TG.TBL_CNT TARGET_TBLCOUNT,
SR.TBL_CNT - TG.TBL_CNT DIFF_COUNT
FROM TBLCOUNT TG,
TBLCOUNT@DBLINK_NAME SR
WHERE TG.TBL_NAME=SR.TBL_NAME )
WHERE DIFF_COUNT <> 0;
;