행위

"오브젝트 비교"의 두 판 사이의 차이

DB CAFE
(→‎= Rows 비교 확인)
 
(같은 사용자의 중간 판 3개는 보이지 않습니다)
1번째 줄: 1번째 줄:
−
--- 오브젝트 확인
+
=== 오브젝트 확인 ===
 
<source lang=sql>
 
<source lang=sql>
 
SELECT DECODE(USERNAME, 'PUBLIC', '_PUBLIC_', USERNAME) USERNAME,
 
SELECT DECODE(USERNAME, 'PUBLIC', '_PUBLIC_', USERNAME) USERNAME,
63번째 줄: 63번째 줄:
 
</source>
 
</source>
  
−
-- 권한 확인
+
=== 권한 확인 ===
 
<source lang=sql>
 
<source lang=sql>
 
SELECT GRANTEE,
 
SELECT GRANTEE,
130번째 줄: 130번째 줄:
 
</source>
 
</source>
  
−
--- INVALID 확인
+
=== INVALID 확인 ===
 
<source lang=sql>
 
<source lang=sql>
 
SELECT OWNER,
 
SELECT OWNER,
138번째 줄: 138번째 줄:
 
FROM  DBA_OBJECTS
 
FROM  DBA_OBJECTS
 
WHERE  STATUS = 'INVALID';
 
WHERE  STATUS = 'INVALID';
 +
</source>
  
−
-- Rows 비교 확인
+
=== Rows 비교 확인 ===
 
-- 양쪽 노드에서 수행
 
-- 양쪽 노드에서 수행
−
<script lang=sql>
+
<source lang=sql>
 
CREATE TABLE SYSTEM.TBLCOUNT(TBL_NAME VARCHAR(50), TBL_CNT NUMBER);
 
CREATE TABLE SYSTEM.TBLCOUNT(TBL_NAME VARCHAR(50), TBL_CNT NUMBER);
 
DECLARE V_SQL VARCHAR2(1000);
 
DECLARE V_SQL VARCHAR2(1000);
148번째 줄: 149번째 줄:
 
WHERE  OWNER IN UPPER( :USERNAME )
 
WHERE  OWNER IN UPPER( :USERNAME )
 
ORDER BY OWNER, TABLE_NAME;
 
ORDER BY OWNER, TABLE_NAME;
−
BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE SYSTEM.TBLCOUNT';
+
 
 +
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 ||'"' ;
 
  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);
 
  DBMS_OUTPUT.PUT_LINE(V_SQL);
159번째 줄: 161번째 줄:
 
</source>
 
</source>
  
−
-- 타겟 디비에서 수행
+
=== 타겟 디비에서 수행 ===
 
<source lang=sql>
 
<source lang=sql>
 
SELECT *
 
SELECT *
172번째 줄: 174번째 줄:
 
;
 
;
 
</source>
 
</source>
 +
[[Category:oracle]]

2020년 10월 13일 (화) 20:32 기준 최신판

thumb_up 추천메뉴 바로가기

1 오브젝트 확인[편집]

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;

2 권한 확인[편집]

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$%';

3 INVALID 확인[편집]

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

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

5 타겟 디비에서 수행[편집]

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=4808"