"오브젝트 비교"의 두 판 사이의 차이
DB CAFE
(새 문서: --- 오브젝트 확인 SELECT DECODE(USERNAME, 'PUBLIC', '_PUBLIC_', USERNAME) USERNAME, COUNT(DECODE(O.OBJECT_TYPE, 'TABLE', O.OBJECT_TYPE, '')) TAB, COUNT(DECODE(O.OB...) |
(→= Rows 비교 확인) |
||
(사용자 2명의 중간 판 6개는 보이지 않습니다) | |||
1번째 줄: | 1번째 줄: | ||
− | + | === 오브젝트 확인 === | |
+ | <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, | ||
60번째 줄: | 61번째 줄: | ||
GROUP BY DECODE(USERNAME, 'PUBLIC', '_PUBLIC_', USERNAME) | GROUP BY DECODE(USERNAME, 'PUBLIC', '_PUBLIC_', USERNAME) | ||
ORDER BY USERNAME; | ORDER BY USERNAME; | ||
+ | </source> | ||
− | + | === 권한 확인 === | |
− | + | <source lang=sql> | |
SELECT GRANTEE, | SELECT GRANTEE, | ||
OWNER||'.'|| TABLE_NAME , | OWNER||'.'|| TABLE_NAME , | ||
126번째 줄: | 128번째 줄: | ||
'OUTLN') ) | 'OUTLN') ) | ||
AND TABLE_NAME NOT LIKE '%BIN$%'; | AND TABLE_NAME NOT LIKE '%BIN$%'; | ||
+ | </source> | ||
− | + | === INVALID 확인 === | |
− | + | <source lang=sql> | |
SELECT OWNER, | SELECT OWNER, | ||
OBJECT_TYPE, | OBJECT_TYPE, | ||
135번째 줄: | 138번째 줄: | ||
FROM DBA_OBJECTS | FROM DBA_OBJECTS | ||
WHERE STATUS = 'INVALID'; | WHERE STATUS = 'INVALID'; | ||
+ | </source> | ||
− | + | === Rows 비교 확인 === | |
-- 양쪽 노드에서 수행 | -- 양쪽 노드에서 수행 | ||
+ | <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); | ||
144번째 줄: | 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'; | ||
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); | ||
153번째 줄: | 159번째 줄: | ||
END; | END; | ||
+ | </source> | ||
− | + | === 타겟 디비에서 수행 === | |
− | + | <source lang=sql> | |
SELECT * | SELECT * | ||
FROM (SELECT TG.TBL_NAME, | FROM (SELECT TG.TBL_NAME, | ||
166번째 줄: | 173번째 줄: | ||
WHERE DIFF_COUNT <> 0; | WHERE DIFF_COUNT <> 0; | ||
; | ; | ||
+ | </source> | ||
+ | [[Category:oracle]] |
2020년 10월 13일 (화) 20:32 기준 최신판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
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;
;