Monday, September 26, 2011

listing invalid objects in schema


select 
OWNER,OBJECT_TYPE,COUNT(OBJECT_ID) TOTAL_INVALID
from dba_objects
where status = 'INVALID'
and owner not in 
('SYS', 'SYSTEM', 'OUTLN', 'OLAPSYS','SYSMAN','EXP_FULL_DATABASE','DATAPUMP_IMP_FULL_DATABASE',
'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'IMP_FULL_DATABASE',
'ORACLE_OCM', 'OEM_MONITOR','CTXSYS', 'DBSNMP', 'IFSSYS',
'IFSSYS$CM', 'WKSYS', 'ORDPLUGINS', 'WMSYS','ORDSYS','MDSYS','OWBSYS','OLAPSYS','EXFSYS',
'OLAP_DBA','SCHEDULER_ADMIN','SPATIAL_CSW_ADMIN_USR','FLOWS_030000','AQ_ADMINISTRATOR_ROLE',
'SPATIAL_WFS_ADMIN_USR','OWB$CLIENT')
GROUP BY OWNER,OBJECT_TYPE
ORDER BY 3 DESC

No comments:

Post a Comment