행위

"마이그레이션 오브젝트 확인"의 두 판 사이의 차이

DB CAFE

(새 문서: [Oracle] 마이그레이션후 오브젝트 확인 --- 오브젝트 확인 SELECT DECODE(USERNAME, 'PUBLIC', '_PUBLIC_', USERNAME) USERNAME, COUNT(DECODE(O.OBJECT_TYPE, 'TABLE'...)
 
1번째 줄: 1번째 줄:
 
[Oracle] 마이그레이션후 오브젝트 확인
 
[Oracle] 마이그레이션후 오브젝트 확인
 
--- 오브젝트 확인
 
--- 오브젝트 확인
 +
<source lang=sql>
 
SELECT DECODE(USERNAME, 'PUBLIC', '_PUBLIC_', USERNAME) USERNAME,
 
SELECT DECODE(USERNAME, 'PUBLIC', '_PUBLIC_', USERNAME) USERNAME,
 
       COUNT(DECODE(O.OBJECT_TYPE, 'TABLE', O.OBJECT_TYPE, '')) TAB,
 
       COUNT(DECODE(O.OBJECT_TYPE, 'TABLE', O.OBJECT_TYPE, '')) TAB,
61번째 줄: 62번째 줄:
 
GROUP BY DECODE(USERNAME, 'PUBLIC', '_PUBLIC_', USERNAME)
 
GROUP BY DECODE(USERNAME, 'PUBLIC', '_PUBLIC_', USERNAME)
 
ORDER BY USERNAME;
 
ORDER BY USERNAME;
−
 
+
</script>
  
 
-- 권한 확인
 
-- 권한 확인
 +
<source lang=sql>
 
SELECT GRANTEE,
 
SELECT GRANTEE,
 
       OWNER||'.'|| TABLE_NAME ,
 
       OWNER||'.'|| TABLE_NAME ,
127번째 줄: 129번째 줄:
 
                       'OUTLN') )
 
                       'OUTLN') )
 
AND    TABLE_NAME NOT LIKE '%BIN$%';
 
AND    TABLE_NAME NOT LIKE '%BIN$%';
−
 
+
</source>
  
 
--- INVALID 확인
 
--- INVALID 확인
 +
<source lang=sql>
 
SELECT OWNER,
 
SELECT OWNER,
 
       OBJECT_TYPE,
 
       OBJECT_TYPE,
136번째 줄: 139번째 줄:
 
FROM  DBA_OBJECTS
 
FROM  DBA_OBJECTS
 
WHERE  STATUS = 'INVALID';
 
WHERE  STATUS = 'INVALID';
−
 
+
</source>
 
--  Rows 비교 확인
 
--  Rows 비교 확인
 
-- 양쪽 노드에서 수행
 
-- 양쪽 노드에서 수행
154번째 줄: 157번째 줄:
  
 
END;
 
END;
−
 
+
</source>
  
 
-- 타겟 디비에서 수행
 
-- 타겟 디비에서 수행
 +
<source lang=sql>
 
SELECT *
 
SELECT *
 
FROM  (SELECT TG.TBL_NAME,
 
FROM  (SELECT TG.TBL_NAME,
167번째 줄: 171번째 줄:
 
WHERE  DIFF_COUNT <> 0;
 
WHERE  DIFF_COUNT <> 0;
 
;
 
;
 +
</source>

2018년 11월 6일 (화) 18:48 판

thumb_up 추천메뉴 바로가기

[Oracle] 마이그레이션후 오브젝트 확인 --- 오브젝트 확인 

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;
</script>

-- 권한 확인
<source lang=sql>
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$%';

--- INVALID 확인 

SELECT OWNER,
       OBJECT_TYPE,
       OBJECT_NAME,
       STATUS
FROM   DBA_OBJECTS
WHERE  STATUS = 'INVALID';

-- Rows 비교 확인 -- 양쪽 노드에서 수행 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; </source>

-- 타겟 디비에서 수행 

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;
;
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=마이그레이션_오브젝트_확인&oldid=679"