What are invalid objects? how do you detect them and how do you resolve them?
select the status of the objects from dba_objects....by
select object_name,object_type from dba_objects where status like 'INVALID';
if you have invalid objects you will get a list otherwise you get no row selected...
if you have invalid objects...
try this script....as sys
set heading off
set pagesize 0
set linesize 79
set verify off
set echo off
decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
A.OBJECT_ID = B.OBJECT_ID(+) and
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
Thanks for the script Thani...
Now i have to find out why we getting these invalid objects..
If a table is referenced by a procedure or triggers is dropped then the procedure or trigger becomes invalid.
Just found out that the sys.order_object_by_dependency is obselete in 8.1.7..even though it exists when a desc is done on it...Hence, to recomiple invalid objects (only procedures and packages) run the utlrp.sql script.
Click Here to Expand Forum to Full Width