다른 명령
오브젝트 확인
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; ;