Compile in Oracle Invalid Objects
Step:-1 Check Invalid Objects
select OWNER, OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS WHERE STATUS = ‘INVALID’ and OWNER in (‘&OWNER’) GROUP BY OWNER, OBJECT_TYPE ORDER BY OBJECT_TYPE;
Step:-2 spool the Invalids list
spool generate_invalids.lst
select owner, object_name, object_type,status,created,last_ddl_time from dba_objects where status = ‘INVALID’ and owner in (‘&owner’) order by object_type, object_name desc
/
spool off
Step:-3 Compile Invalid Objects
spool recompile_invalids.lst
select ‘alter ‘||decode(object_type,’PACKAGE BODY’,’PACKAGE’,object_type) || ‘ ‘ ||owner|| ‘.”‘||object_name||’” ‘||decode(object_type,’PACKAGE BODY’,’COMPILE BODY’,’compile’)|| ‘ ; ‘
from dba_objects where object_type in (‘PACKAGE’,’PACKAGE BODY’,’PROCEDURE’,’VIEW’, ‘TRIGGER’,’FUNCTION’,’SYNONYM’) and status=’INVALID’ and owner in (‘&owner’)
/
spool off
Step:4 Execute from sqlplus @recompile_invalids.lst
Step:-5 Validation
select owner, object_name, object_type,status,created,last_ddl_time from dba_objects where owner in (‘&owner’) and trunc(last_ddl_time) = trunc(sysdate) order by object_type, object_name
/
