-- Oracle Database Invalid Objects can be recompiled using pl/sql block
-- or a procedure as shown below.

CREATE OR REPLACE PROCEDURE Compile_All_Invalid(o_status OUT VARCHAR2) AS 
 CURSOR c_inv_obj IS
  SELECT  ao.owner
  FROM    all_objects ao
  WHERE   NOT EXISTS
   (SELECT NULL
    FROM   v$access a,
           v$session s
    WHERE  a.sid = s.sid
    AND    a.object = ao.object_name)
  AND     ao.object_type IN 
           ('FUNCTION','PROCEDURE','PACKAGE','TYPE','MATERIALIZED VIEW')
  AND     ao.status = 'INVALID';

 BEGIN 
  FOR obj_rec IN c_inv_obj LOOP
   DBMS_UTILITY.COMPILE_SCHEMA(schema         => obj_rec.owner,
                               compile_all    => TRUE,
                               reuse_settings => TRUE);
  END LOOP;
 EXCEPTION 
  WHEN OTHERS THEN
  o_status := SQLERRM;
  RAISE; 
END Compile_All_Invalid; 
/

-- If an object is being used by an application, ETL process or any BI
-- report, then it will lock the session when it tries to compile the
-- locked object.  The above code will avoid the session from being 
-- locked when compiling and compile all currently inactive objects.

-- Materialized views take long to recompile and can be executed separately


DECLARE
 CURSOR c_inv_obj IS
  SELECT  ao.owner
  FROM    all_objects ao
  WHERE   NOT EXISTS
   (SELECT NULL
    FROM   v$access a,
           v$session s
    WHERE  a.sid = s.sid
    AND    a.object = ao.object_name)
  AND     ao.object_type = 'MATERIALIZED VIEW'
  AND     ao.status = 'INVALID';

 BEGIN 
  FOR obj_rec IN c_inv_obj LOOP
   DBMS_UTILITY.COMPILE_SCHEMA(schema         => obj_rec.owner,
                               compile_all    => TRUE,
                               reuse_settings => TRUE);
  END LOOP;
 EXCEPTION 
  WHEN OTHERS THEN
  o_status := SQLERRM;
  RAISE; 
END Compile_All_Invalid; 
/