마이그레이션 오브젝트 확인

[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;

-- 권한 확인

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;

-- 타겟 디비에서 수행

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

마이그레이션 오브젝트 확인

DB CAFE