Home » Oracle » Oracle Database » How to Compile Invalid objects in Oracle

How to Compile Invalid objects in Oracle

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 or 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

How to Find invalid objects in the Oracle database

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

See also  Weblogic Administration Console

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
See also  How to check automatic statistics collection in Oracle in 11g

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 compile 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 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.

See also  How to find the waitevent History of the Oracle session

I hope you like this article on how to compile invalid objects in oracle. Please do comment and Provide the feedback

Also Read
How to List All Tables in Oracle
Query to check table size in Oracle
how to find indexes on a table in oracle
change date format in oracle
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:637156098168

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top