Validate Invalid Objects

A) 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';

B) Try Manual method of validation:

Alter procedure <owner>.<procedure_name> compile;

Alter function <owner>.<function_name> compile;

Alter view <owner>.<view_name> compile;

Alter package <owner>.<package_name> compile;

Alter package <owner>.<package_name> compile body;

Alter materialized view <owner>.<Package_name> 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 <package_name> compile body;

Another SQL Script: To Recompile Invalid Objects

set heading off
 set pagesize 0
 set linesize 79
 set verify off
 set echo off
 spool comp_all.sql
     'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
     'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
     dba_objects a,
     sys.order_object_by_dependency b
     A.OBJECT_ID = B.OBJECT_ID(+) and
     STATUS = 'INVALID' and
                       'TRIGGER', 'VIEW' )
 order by
 spool off

About Sher khan

Senior Oracle DBA, Oracle 10g , Oracle 11g OCE ,Oracle 12C and Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g , 11g and 12C. I am working in Oracle technology since 2004. Currently working in U.A.E Email:
Aside | This entry was posted in Performance and Tuning. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s