Script check for Compare Object on Oracle database 10g onward
When you do maintenance, upgrade and migration. You have to keep data before and after to compare to the same thing.
- For count object
| SELECT OWNER,OBJECT_TYPE,COUNT(*) FROM DBA_OBJECTS WHERE OWNER NOT IN (‘SYS’,’SYSTEM’,’SYSMAN’,’ANONYMOUS’, ‘APPQOSSYS’,’DBSNMP’, ‘EXFSYS’,’MDSYS’,’ORACLE_OCM’ ,’ORDSYS’ ,’OUTLN’ ,’SYSTEM_LOAD_TEST’,’TSMSYS’,’WMSYS’,’XDB’,’XS$NULL’,’ORDDATA’,’CTXSYS’) GROUP BY OWNER,OBJECT_TYPE ORDER BY OWNER,OBJECT_TYPE; |
2. For counting objects by status
| SELECT OWNER,OBJECT_TYPE,STATUS,COUNT(*) FROM DBA_OBJECTS WHERE OWNER NOT IN (‘SYS’,’SYSTEM’,’SYSMAN’,’ANONYMOUS’, ‘APPQOSSYS’,’DBSNMP’, ‘EXFSYS’,’MDSYS’,’ORACLE_OCM’ ,’ORDSYS’ ,’OUTLN’ ,’SYSTEM_LOAD_TEST’,’TSMSYS’,’WMSYS’,’XDB’,’XS$NULL’,’ORDDATA’,’CTXSYS’) GROUP BY OWNER,OBJECT_TYPE,STATUS ORDER BY OWNER,OBJECT_TYPE,STATUS; |
3. Show an Invalid object
| SELECT OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS FROM DBA_OBJECTS WHERE OWNER NOT IN (‘SYS’,’SYSTEM’,’SYSMAN’,’ANONYMOUS’, ‘APPQOSSYS’,’DBSNMP’, ‘EXFSYS’,’MDSYS’,’ORACLE_OCM’ ,’ORDSYS’ ,’OUTLN’ ,’SYSTEM_LOAD_TEST’,’TSMSYS’,’WMSYS’,’XDB’,’XS$NULL’,’ORDDATA’,’CTXSYS’) AND STATUS=’INVALID’ ORDER BY OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS ; |