Compile all invalid Materialized Views in a schema

select 'alter '||object_type||' '||owner||'."'||object_name||'" compile;'
from dba_objects where owner = 'OWNER' and object_type = 'MATERIALIZED VIEW' and status <> 'VALID'

OR

SET SERVEROUTPUT ON 
BEGIN
  FOR i IN (SELECT owner,object_name, object_type FROM   dba_objects
                  WHERE  object_type IN ('MATERIALIZED VIEW')
                  AND    status <> 'VALID'
                  AND OWNER='SCHEMA NAME'
                  ORDER BY 2)
  LOOP
    BEGIN
      IF i.object_type = 'MATERIALIZED VIEW' THEN
        EXECUTE IMMEDIATE 'ALTER ' || i.object_type ||' "' || i.owner || '"."' || i.object_name || '" COMPILE';
    
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(i.object_type || ' : ' || i.owner ||' : ' || i.object_name);
    END;
  END LOOP;
END;

You can use the following in SQLPLUS if need to compile all objects in schema

exec dbms_utility.compile_schema('SCHEMA NAME')

Advertisements