Recompile invalid objects. | Oracle

Check for invalids

COLUMN object_name FORMAT A30
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

Schema level:

select object_name,object_type,status from user_objects where status='INVALID' order by object_type;


Recompile invalids: DBA

@?/rdbms/admin/utlrp.sql  --- Recompiles all objects in Database.
@?/rdbms/admin/utlprp.sql --- This script will make all objects in the database as invalid and then recompiles. Use with caution. 

 Use the following queries to track recompilation progress:

   1. Query returning the number of invalid objects remaining. This number should decrease with time.
         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

   2. Query returning the number of objects compiled so far. This number should increase with time.
         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;



Compile Schema

BEGIN
    DBMS_UTILITY.COMPILE_SCHEMA(schema => USER, compile_all => FALSE);
END;

/

Compile manually for small number of invalids.

ALTER PACKAGE <package_name> COMPILE;

ALTER PACKAGE <package_name> COMPILE BODY;

ALTER PROCEDURE <procedure_name> COMPILE;

ALTER FUNCTION <function_name> COMPILE;

ALTER TRIGGER <trigger_name> COMPILE;

ALTER VIEW <view_name> COMPILE;

Recompile all Functions in schema. For Database level change the user_objects (highlighted) to DBA_OBJECTS.

Below script identifies the invalids in the schema/database of object type and recompile them.

BEGIN
        FOR c IN (
                SELECT object_name
                FROM user_objects
                WHERE object_type = 'FUNCTION'
                AND status = 'INVALID'
        )
        LOOP
                dbms_utility.exec_ddl_statement('ALTER FUNCTION '||c.object_name||' COMPILE');
        END LOOP;
END;
/

PROCEDURE 

BEGIN

        FOR c IN (
                SELECT object_name
                FROM user_objects
                WHERE object_type = 'PROCEDURE'
                AND status = 'INVALID'
        )
        LOOP
                dbms_utility.exec_ddl_statement('ALTER PROCEDURE '||c.object_name||' COMPILE');
        END LOOP;
END;
/

PACKAGE

BEGIN

        FOR c IN (
                SELECT object_name
                FROM user_objects
                WHERE object_type = 'PACKAGE'
                AND status = 'INVALID'
        )
        LOOP
                dbms_utility.exec_ddl_statement('ALTER PACKAGE '||c.object_name||' COMPILE');
        END LOOP;
END;
/

PACKAGE BODY

BEGIN

        FOR c IN (
                SELECT object_name
                FROM user_objects
                WHERE object_type = 'PACKAGE BODY'
                AND status = 'INVALID'
        )
        LOOP
                dbms_utility.exec_ddl_statement('ALTER PACKAGE '||c.object_name||' COMPILE BODY');
        END LOOP;
END;
/

VIEW

BEGIN

        FOR c IN (
                SELECT object_name
                FROM user_objects
                WHERE object_type = 'VIEW'
                AND status = 'INVALID'
        )
        LOOP
                dbms_utility.exec_ddl_statement('ALTER VIEW '||c.object_name||' COMPILE');
        END LOOP;
END;
/

TRIGGER

BEGIN

        FOR c IN (
                SELECT object_name
                FROM user_objects
                WHERE object_type = 'TRIGGER'
                AND status = 'INVALID'
        )
        LOOP
                dbms_utility.exec_ddl_statement('ALTER TRIGGER '||c.object_name||' COMPILE');
        END LOOP;
END;
/