Oracle Scripts To Recompile Invalid Objects


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.   🙂

Advertisements

One Response to Oracle Scripts To Recompile Invalid Objects

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: