One of the classic problems a DBA can have is invalid objects. If a view becomes invalid, any other objects using that view, such as a procedure, also become invalid. Then when the procedure is called, it will not run.
Some scripts that have been useful to me are:
invalid.sql:
/*
Script to list invalid objects
By Rodger Lepinsky
*/
select
decode(object_type, ‘PACKAGE BODY’, ‘PACKAGE’, object_type) ||
‘ ‘ || owner || ‘.’ ||
object_name
as invalid_objects
from ALL_objects
where status = ‘INVALID’ and object_type in
(‘PROCEDURE’,’FUNCTION’,’PACKAGE’,’PACKAGE BODY’,’TRIGGER’,’VIEW’)
ORDER BY 1
/
————
recompile.sql
/*
Script to generate the SQL commands needed to recompile invalid Oracle objects.
By Rodger Lepinsky
*/
select ‘alter ‘ ||
decode(object_type, ‘PACKAGE BODY’, ‘PACKAGE’, object_type) ||
‘ ‘ || owner || ‘.’ ||
object_name || ‘ compile ‘ ||
decode(object_type, ‘PACKAGE BODY’, ‘BODY’,”) ||’;’
AS RECOMPILE
from ALL_objects
where status = ‘INVALID’
and object_type in
(‘PROCEDURE’,’FUNCTION’,’PACKAGE’,’PACKAGE BODY’,’TRIGGER’,’VIEW’)
ORDER BY 1
/
I’ve often used these scripts after refreshing a database.
These are so simple, you shouldn’t have needed to search for it. 🙂
tnx bro