Recompiling Invalid/All schema objects in the database

Identifying Invalid Objects: Use all_objects table to identity invalid schema objects. Oracle has given the default pkg to recompiling invalid/all schema objects in the database.

SELECT owner,
       object_type,
       object_name,
       status
  FROM all_objects
 WHERE status = 'INVALID'
 ORDER BY owner,
          object_type,
          object_name;

Custom Script: When you have to compile "N" no of invalid objects in one go, the best approach is to write custom script as below,

BEGIN
  FOR cur IN(SELECT object_name,
                    object_type,
                    owner
               FROM sys.all_objects
              WHERE object_type   = 'MY_OBJECTS'
                AND owner         = 'MY_SCHEMA'
                AND status        = 'INVALID' -- For all objects, it should be excluded
             )
  LOOP
    BEGIN
      IF cur.object_type = 'PACKAGE BODY' THEN
        EXECUTE IMMEDIATE 'alter ' || cur.object_type || ' "' || cur.owner || '"."' || cur.object_name || '" compile body';
      ELSE
        EXECUTE IMMEDIATE 'alter ' || cur.object_type || ' "' || cur.owner || '"."' || cur.object_name || '" compile';
      END IF;
    EXCEPTION
    WHEN OTHERS THEN
      NULL;
    END;
  END LOOP;

END;

Manual Execution: Manual execution is sufficient, when you have to compile small no of objects.

• ALTER PROCEDURE <<my_procedure>> COMPILE;
• ALTER FUNCTION <<my_function>> COMPILE;
• ALTER PACKAGE <<my_package>> COMPILE;
• ALTER PACKAGE <<my_package>> COMPILE BODY;
• ALTER VIEW <<my_view>> COMPILE;
• ALTER TRIGGER <<my_trigger>> COMPILE;

DBMS_DDL.alter_compile: An alternative approach is to use the DBMS_DDL pkg,

• exec dbms_ddl.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');
• exec dbms_ddl.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');
• exec dbms_ddl.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
• exec dbms_ddl.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');
• exec dbms_ddl.alter_compile('VIEW', 'MY_SCHEMA', 'MY_VIEW');
• exec dbms_ddl.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');

DBMS_UTILITY.compile_schema: The DBMS_UTILITY.compile_schema procedure used to compiles all procedures, functions, packages, views, and triggers in the specified schema.

exec dbms_utility.compile_schema(SCHEMA => 'FXO', compile_all => FALSE);