Last updated on October 30th, 2015 at 06:05 pm
Invalid object compilation steps
Find Invalid Objects:1.To Find the number of invalid objects :
select count(*) from dba_objects where status=’INVALID’;
select owner,object_type,count(*) from dba_objects where status=’INVALID’ group by owner,object_type;
2.To identify the object name and their types and owner:
select owner, object_name,object_type from dba_objects where status =’INVALID’;
Try Manual method of validation:
Alter procedure . compile;
Alter function . compile;
Alter view . compile;
Alter package . compile;
Alter package . compile body;
Alter materialized view . Compile;
In case you have lots of invalid objects, you can generate scripts that will generate the sqls for compiling the invalid objects :
In sqlplus connect as sys:
set heading off
select ‘alter ‘||object_type|| ‘ ‘ || owner ||’.’||object_name || ‘ compile;’ from dba_objects where status=’INVALID’;
Then run compileinvalid.sql in sqlplus prompt as sys user.
To compile invalid package body use:
alter package compile body;
Related: How to monitor rollback progress
How to Find the Compilation Errors:
If any of compile statement returns warning of form:
Warning: Procedure created with compilation errors.
Warning: View created with compilation errors.
Warning: Function created with compilation errors.
Warning: Package created with compilation errors.
Oracle supplied script method to compile invalid
We can recompile invalid objects using oracle supplied script
This jobs create parallel compile job processes in dba_schedular.
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