Compile Database Objects - 1

There are multiple types of objects in database and different ways of compilation. There are different states for the objects and what we usually want them is to be "VALID".
To check the status of some object we can query user_objects, dba_objects views. Below query may show you the information required.

set linesize 120
column owner format a20
column object_name format a54
column object_type format a22
column status format a15
select owner, object_name, object_type, status from dba_objects where object_name like '%&Object_Name%';

Mainly what we have with status 'INVALID' are the Packages, Package Bodys, Procedures, Function, Synonyms, Views, Trigger. The reason most of the time is the objects being used in their defination gets changed or dropped but may have many more reasons.

The basic command one can use for object compilation is
Alter <Object> <Object Name> compile;

It can be used as below for these objects:-
Alter Package <Package Name> Compile; -- Compile Specification
Alter Package <Package Name> Compile Body; -- Compile Body
Alter Procedure <Procedure Name> Compile; -- Procedure
Alter Function <Function Name> Compile; -- Function
Alter Synonym <Synonym Name> Compile; -- Synonym
Alter View <View Name> Compile; -- View
Alter Trigger <Trigger Name> Compile; -- Trigger

For compilation of multiple objects we can use below:-

To check the no. of invalid objects a simple sql is
select count(1) from dba_objects where status = 'INVALID';

You have to run below as sysdba to compile all the objects in database.
exec utl_recomp.recomp_parallel(<No. of parallel Threads>);

Now if we will again check the count of invalids and if some of them are compiled successfully, you will find count is less then earlier.

To compile some particular schema/user related objects you can use below.
exec utl_recomp.recomp_parallel(<No. of parallel Threads>,'<Your Schema Name>');
or
exec dbms_utility.compile_schema('<Your Schema Name>');

Parallelism if used 1 then it is similar to utl_recomp.recomp_serial().

For Apps Schema compilation in Oracle Apps, you can also use ADADMIN utility.
There is a little different approach for other objects like Invalid Indexes and Materialized Views. Please click here to know more.