How to find Invalid Objects and Compile in Oracle
Objects that requires recompilation are
----------------------------------------------------
VIEW, SYNONYM, PUBLIC SYNONYM, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, UNDEFINED , JAVA CLASS, TYPE, TYPE BODY
Find Invalid Objects
---------------------------
set pages 50000 lines 32767
select OWNER, OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS WHERE STATUS = 'INVALID' and OWNER in ('&OWNER') GROUP BY OWNER, OBJECT_TYPE ORDER BY OBJECT_TYPE;
SPOOL The Invalids list
---------------------------
spool generate_invalids.lst
set pages 50000 lines 32767
col owner format a15
col object_type format a20
col object_name format a30
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
Recompile 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
@recompile_invalids.lst
Validation
----------
set pages 50000 lines 32767
col owner format a15
col object_type format a20
col object_name format a30
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
/
exit
------------------------------------------------------------------------------------------------------------------
MORE QUERIES
Generate Oracle scripts to compile procedure , function , package , package body,trigger, view :-
select
decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from
dba_objects a
where
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
and owner='SYS'
order by
OBJECT_TYPE,
OBJECT_NAME;
------------------------------------------------------------------------------------------------------------------
set heading off;
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set tab on;
set scan off;
set verify off;
--
SPOOL gen_inv_obj.sql;
select
decode (OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || a.OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || a.OWNER||'.'||OBJECT_NAME || '
compile;')
from dba_objects a,
(select max(level) order_number, object_id from public_dependency
connect by object_id = prior referenced_object_id
group by object_id) b
where A.object_id = B.object_id(+)
and STATUS = 'INVALID'
and OBJECT_TYPE in ('PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE','TRIGGER', 'VIEW')
order by
order_number DESC,OBJECT_TYPE,OBJECT_NAME;
SPOOL off;
@gen_inv_obj.sql;
------------------------------------------------------------------------------------------------------------------
set serverouput on
DECLARE
comp_pack VARCHAR2 (100);
comp_pack_body VARCHAR2 (200);
comp_view VARCHAR2 (200);
comp_proc VARCHAR2 (200);
comp_trig VARCHAR2 (200);
comp_func VARCHAR2 (200);
BEGIN
FOR c IN ( SELECT * FROM dba_objects WHERE status = 'INVALID' ORDER BY object_type)
LOOP
BEGIN
--generate compile statement
comp_pack :='alter package '|| c.owner|| '.'|| c.object_name|| ' compile;';
comp_pack_body :='alter package '|| c.owner|| '.'|| c.object_name|| ' compile body;';
comp_view :='alter view ' || c.owner || '.' || c.object_name || ' compile;';
comp_proc :='alter procedure '|| c.owner|| '.'|| c.object_name|| ' compile;';
comp_func :='alter function '|| c.owner|| '.'|| c.object_name|| ' compile;';
comp_trig :='alter trigger '|| c.owner|| '.'|| c.object_name|| ' compile;';
DBMS_OUTPUT.put_line ('Compile -> ' || c.object_name || ' type : ' || c.object_type);
--compile
IF c.object_type = 'PACKAGE' THEN
EXECUTE IMMEDIATE comp_pack;
ELSIF c.object_type = 'PACKAGE BODY' THEN
EXECUTE IMMEDIATE comp_pack_body;
ELSIF c.object_type = 'VIEW' THEN
EXECUTE IMMEDIATE comp_view;
ELSIF c.object_type = 'PROCEDURE' THEN
EXECUTE IMMEDIATE comp_proc;
ELSIF c.object_type = 'FUNCTION' THEN
EXECUTE IMMEDIATE comp_func;
ELSIF c.object_type = 'TRIGGER' THEN
EXECUTE IMMEDIATE comp_trig;
END IF;
--catch exception and show
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Compile ERROR : '|| c.owner|| '.'|| c.object_name|| ' type => '|| c.object_type);
END;
END LOOP;
END;
