Invalid object compilation steps



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

spool compileinvalid.sql

select ‘alter ‘||object_type|| ‘ ‘ || owner ||’.’||object_name || ‘ compile;’ from dba_objects where status=’INVALID’;

spool off

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

@$ORACLE_HOME/rdbms/admin/utlrp.sql

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


Leave a Reply