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
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
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
We can recompile invalid objects using oracle supplied script
- 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
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
TRUE to compile everything within the schema regardless of whether status is VALIDFALSE to compile only objects with status INVALID
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
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.
I hope you like this article on how to compile invalid objects in oracle. Please do comment and Provide the feedback
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