Objects in the database can become invalid, if there are any changes to the dependent objects. Even patch and upgrade can also make the object invalid. Compiling the invalid objects can make them valid if they are eligible.

Below is the query to find the invalid objects currently present in the database.

set pagesize 200
set lines 200
select owner,object_name,object_type,status from dba_objects where STATUS='INVALID';

1. Compile all the objects of the database using UTLRP.SQL.

This script may take some time, depending on the number of objects.

@$ORACLE_HOME/rdbms/admin/utlrp.sql

2. Compile objects of a particular schema:

EXEC DBMS_UTILITY.compile_schema(schema => 'APPS');

3. Compiling individual objects:

-- Compiling a package;

ALTER PACKAGE APPS.DAIL_REPORT COMPILE;

ALTER PACKAGE APPS.DAIL_REPORT COMPILE BODY;

-- Compiling a procedure:

ALTER PROCEDURE APPS.REPORT_PROC COMPILE;

-- Compiling a view:

ALTER VIEW APPS.USERSTATUS_VW COMPILE;

-- Compiling a function:

ALTER FUNCTION APPS.SYNC_FUN COMPILE;