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
