Recompiling invalid Objects in a database

Encountering Invalid Objects in database is a common problem to everyone.
Use the scripts below to recompile the objects.Each script is best suited for a given no. of invalid objects

Invcheck.sql:Script used to check the invalid schema objects

Set heading off; set feedback off; set echo off; Set lines 999;

Select 'No of Invalid Pkg specs and bodys : '||

(SELECT count(*) FROM dba_objects WHERE status = 'INVALID' AND object_type in ('PACKAGE','PACKAGE BODY') AND (object_name like 'CSI%'or object_name like 'OE_%'))

FROM dual;

SELECT owner AS Schema,object_name AS object,object_type AS Type,status,last_ddl_time AS lastModified FROM dba_objects WHERE status = 'INVALID' AND object_type in ('PACKAGE','PACKAGE BODY') AND (object_name like 'CSI_%'or object_name like 'OE_%');

show errors;

exit;

Invrecomp.sql

set feedback off;
set echo off;
Set lines 999;

Select 'No of Invalid Pkg specs and bodys : '||
(SELECT count(*) FROM dba_objects WHERE status = 'INVALID' AND object_type in ('PACKAGE','PACKAGE BODY') AND (object_name like 'CSI%'or object_name like 'OE_%'))
FROM dual;


Spool run_invalid.sql

select
'ALTER ' || OBJECT_TYPE || ' ' ||
OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from
dba_objects
where
status = 'INVALID'
and
object_type in ('PACKAGE','FUNCTION','PROCEDURE')
and
(object_name like 'CSI%'or object_name like 'OE_%')
;

spool off;

set heading on;
set feedback on;
set echo on;

@run_invalid.sql
show errors;

exit;

Invrecompcur.sql

Set heading off;
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
FOR cur_rec IN (SELECT owner,
object_name,
object_type,
DECODE(object_type, 'PACKAGE', 1,
'PACKAGE BODY', 2, 2) AS recompile_order
FROM dba_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY')
AND status != 'VALID'
AND (object_name like 'CSI%'or object_name like 'OE_%')
ORDER BY 4)
LOOP
BEGIN
IF cur_rec.object_type = 'PACKAGE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
ElSE
EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||
'"."' || cur_rec.object_name || '" COMPILE BODY';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||
' : ' || cur_rec.object_name);
END;
END LOOP;
END;
/
commit;
show errors;
exit;

Invrecompdep.sql

set serveroutput on size 1000000

declare
sql_statement varchar2(200);
cursor_id number;
ret_val number;
begin

dbms_output.put_line(chr(0));
dbms_output.put_line('Re-compilation of Invalid Objects');
dbms_output.put_line('---------------------------------');
dbms_output.put_line(chr(0));

for invalid in (select object_type, owner, object_name
from sys.dba_objects o,
sys.order_object_by_dependency d
where o.object_id = d.object_id(+)
and o.status = 'INVALID'
and o.object_type in ('PACKAGE', 'PACKAGE BODY',
'FUNCTION',
'PROCEDURE', 'TRIGGER',
'VIEW')
order by d.dlevel desc, o.object_type) loop

if invalid.object_type = 'PACKAGE BODY' then
sql_statement := 'alter package '||invalid.owner||'.'||invalid.object_name||
' compile body';
else
sql_statement := 'alter '||invalid.object_type||' '||invalid.owner||'.'||
invalid.object_name||' compile';
end if;

/* now parse and execute the alter table statement */
cursor_id := dbms_sql.open_cursor;
dbms_sql.parse(cursor_id, sql_statement, dbms_sql.native);
ret_val := dbms_sql.execute(cursor_id);
dbms_sql.close_cursor(cursor_id);

dbms_output.put_line(rpad(initcap(invalid.object_type)||' '||
invalid.object_name, 32)||' : compiled');
end loop;

end;
/
commit;
show errors;
exit;
Runinvalid.sql

Set heading off;
set feedback off;
set echo off;
Set lines 999;

Spool run_invalid.sql

select
'ALTER ' || OBJECT_TYPE || ' ' ||
OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from
dba_objects
where
status = 'INVALID'
and
object_type in ('PACKAGE','FUNCTION','PROCEDURE')
and
(object_name like 'MTL%'or object_name like 'OE_%')
;

spool off;

set heading on;
set feedback on;
set echo on;

@run_invalid.sql
show errors;