How to Compile Invalid objects in Oracle

Oracle database is a complex system and objects are interrelated and have dependencies. Like a package may depend on a certain Oracle table, view or synonym. So objects may go invalid in case of deployments of new objects, alter table commands, packages, views, and synonyms. Now let’s find out how to find the invalid objects in the Oracle database and then how to compile invalid objects in Oracle like How to compile the view in Oracle, compile package body in Oracle, compile procedure in Oracle, compile trigger in Oracle, etc

Table of Contents

How to Find invalid objects in the Oracle database

The following queries can be used to find out the invalid objects

As a DBA role

select count(*) from dba_objects where status='INVALID';

select owner,object_type,count(*) from dba_objects 
where status='INVALID' group by owner,object_type;
 
select owner, object_name,object_type from dba_objects 
 where status ='INVALID';

As a particular user

select count(*) from user_objects 
 where status='INVALID';

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

If the database is Multitenant, we need to make sure we are in the right container before executing these commands. We can either connect to the pluggable database using the service name or we can connect as sysdba and then switch to that container

How to compile invalid objects in Oracle

Now once we can find the invalid objects, there are many ways to compile the invalid objects
(a) Manual compilation
(b) Using UTLRP.sql
(c) Using DBMS_UTILITY
Option (b) and (c) can be used with the DBA role but Option (1) can be done by the individual users also

In each of the above, please make sure you are in the right pluggable database in the case of the Multitenant database

Manual Compilation

How to compile the view in Oracle

Alter view <view name> compile;

How to compile Procedure in Oracle

Alter procedure <procedure name> compile;

How to compile Package and Package body in Oracle

First, compile the package specification, and then the body

Alter package <package name> compile;
Alter package <package name> compile body;

How to compile Function in Oracle

Alter function <function name> compile;

How to compile materialized view

Alter materialized view <mview name> Compile;

How to compile synonym

alter synonym  <name> compile;
alter public synonym  <name> compile;

How to compile Trigger

Alter  trigger  <trigger name> Compile;

In case you are doing a compilation with a DBA user, we need to put the owner name in this alteration command

In case you have lots of invalid objects, you can generate scripts that will generate the SQL for compiling the invalid objects

Generating the list and compiling all the objects

 set heading off
 spool compileinvalid.sql
 select 'alter '||object_type|| ' ' || owner ||'.'||object_name || ' compile;' from dba_objects where status='INVALID';
 spool off
@compileinvalid.sql

Compiling just the package, package body, and Procedure

 set heading off;
 set feedback off;
spool inv_comp.sql
 select 'Alter ' || decode(object_type,'PACKAGE BODY','PACKAGE',object_type) || ' ' || object_name || ' compile ' || decode(object_type,'PACKAGE BODY',' body;',';') from user_objects where object_type in ('PACKAGE','PACKAGE BODY','PROCEDURE') and status = 'INVALID' order by object_type , object_name;
 set heading on;
 set feedback on;
 /
@inv_comp.sql

Using UTLRP.sql

We can recompile invalid objects using oracle supplied script

@$ORACLE_HOME/rdbms/admin/utlrp.sql

These jobs create parallel compile job processes in dba_scheduler.
The compile jobs are run by job queue processes

We must have a good number of job_queue_processes on Multiple Cpu systems to reduce the compilation time
This uses internally the below package to perform the compilation
UTL_RECOMP.RECOMP_PARALLEL

Using DBMS_UTILITY

This procedure compiles all procedures, functions, packages, and triggers in the specified schema.

DBMS_UTILITY.COMPILE_SCHEMA (
    schema          IN VARCHAR2,
    compile_all     IN BOOLEAN DEFAULT TRUE,
    reuse_settings  IN BOOLEAN DEFAULT FALSE);

schema: Name of the schema

compile_all :
TRUE to compile everything within the schema regardless of whether status is VALIDFALSE to compile only objects with status INVALID

reuse_settings:
Flag to specify whether the session settings in the objects should be reused, or the current session settings should be adopted instead

SQL> exec dbms_utility.compile_schema('SCOTT','FALSE');
 PL/SQL procedure successfully completed.

How to Find the Compilation Errors

If any of these compiled statements returns a warning of the form below while doing a manual compilation
Warning: Procedure created with compilation errors.
Warning: View created with compilation errors.
Warning: Function created with compilation errors.
Warning: Package created with compilation errors.
or It remains invalid even after compilation through the utlrp and dbms_utility, then you can look at the errors from the below sql and take corrective action accordingly

 SELECT text
 FROM dba_errors
 WHERE name = '&packagename';

The output will look something like this.

 TEXT
-----
 PLS-00302: component 'INSERT_BATCH_LINES' must be declared
 PL/SQL: Statement ignored

or below

 
SHOW ERRORS <type> <schema>.<name> 
example
Show errors package SCOTT.TEST_PKG

After you have compiled all the packages and found the errors from the remaining invalid objects. You can use the below queries to extract the code of these invalid objects and fix them accordingly. You can use the below

select source from dba_source where name='&Name';

  Set long 20000;
 SELECT table_name, trigger_name, trigger_body
 FROM dba_triggers
 WHERE table_name = upper ('table name');
 or
 WHERE trigger_name = upper ('trigger name');

How to find invalid Java classes in oracle

SQL>SELECT object_name,object_type,owner,status FROM dba_objects
WHERE object_type='JAVA CLASS' AND status='INVALID';

How to resolve invalids for JAVA

Suppose the above query gave the below results

OBJECT_NAME OBJECT_TYPE OWNER STATUS
-----------  ----------  -----------
/b0993e19_MyAppsContext JAVA CLASS APPS INVALID
oracle/apps/fnd/common/FileLog JAVA CLASS APPS INVALID

We can try to resolve these invalids using the following statements.

SQL>ALTER JAVA CLASS "/b0993e19_MyAppsContext" RESOLVE;
SQL> ALTER JAVA CLASS "oracle/apps/fnd/common/FileLog" RESOLVE;

Note: If you don’t include java class names in double-quotes the statements will fail.