Oracle database is a complex system and objects are inter-related and have dependencies.Like a package may depends on certain oracle table or view or synonym. So objects may go invalid in case of deployments of new objects , alter table command, packages,views, synonyms. Now lets find out how to find the invalid objects in the Oracle database and the compile the invalid objects
How to Find invalid objects in 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';
How to Compile the invalid Objects
Now once we can found 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 DBA role but Option (1) can be done by individual user also
How to compile the view
Alter view <view name> compile;
How to compile Procedure
Alter procedure <procedure name> compile;
How to compile Package and Package body
First compile the package specification and then body
Alter package <package name> compile; Alter package <package name> compile body;
How to compile Function
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;
In-case you are doing compilation with DBA user, we need to put the owner name in these alteration command
In case you have lots of invalid objects, you can generate scripts that will generate the sqls 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
This jobs create parallel compile job processes in dba_scheduler.
The compile jobs are run by job queue processes
We must have good number of job_queue_processes on Multiple Cpu system 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 statement returns warning of form below while doing 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 remaining invalid objects. You can use 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');
I hope you like this Article.Please do comment and Provide the feedback