-- =======================================================================
--  compile any object view 
-- =======================================================================
select object_name, object_type, status, owner,
'ALTER '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||' COMPILE;' 
from dba_objects
where status <> 'VALID'
and owner NOT IN  ('SYS', 'SYSTEM', 'PUBLIC','OUTLN','DBSNMP','WMSYS','SYSMAN') 
and owner not like '%SYS%' and owner like 'DBSVS%'
and object_type  IN  ('SEQUENCE','TRIGGER','PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','VIEW','MATERIALIZED VIEW')
--and object_name like '%037%'
and object_type = 'MATERIALIZED VIEW'

-- =======================================================================
--  compile materialized view
-- =======================================================================
SELECT 'ALTER MATERIALIZED VIEW '||A.OWNER||'.'||TABLE_NAME||' COMPILE;' 
FROM DBA_SNAPSHOTS a --WHERE a.OWNER = ''