Validate Invalid Objects in Oracle Database

 Validate Invalid Objects in Oracle Database

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

 select

     decode( OBJECT_TYPE, 'PACKAGE BODY',

     'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',

     'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )

 from

     dba_objects a,

     sys.order_object_by_dependency b

 where

     A.OBJECT_ID = B.OBJECT_ID(+) and

     STATUS = 'INVALID' and

     OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',

                       'TRIGGER', 'VIEW' )

 order by

     DLEVEL DESC,

     OBJECT_TYPE,

     OBJECT_NAME;

 spool off