gcompile.sql – Generate a list of compile statements

I’ve never had much joy with utlrp, and I’m too lazy to work out why.

Anyway, this works for me:

set pagesize 0
spool compile.sql
select decode(object_type,
‘PACKAGE BODY’,
‘alter ‘ || ‘package ‘ ||’ ‘ ||
owner ||’.’ || object_name ||’ compile body;’ ||
CHR(10) ||
‘sho err’,
‘TYPE BODY’,
‘alter ‘ || ‘type ‘ ||’ ‘ ||
owner ||’.’ || object_name ||’ compile body;’ ||
CHR(10) ||
‘sho err’,
— OTHER STUFF
‘alter ‘ || object_type ||’ ‘ ||
owner ||’.’ || object_name ||’ compile;’ ||
CHR(10) ||
‘sho err’)
from dba_objects
where status = ‘INVALID’
and owner like ‘%&owner%’
— and owner not in (‘SPOT’, ‘IMON_USER’, ‘PERFSTAT’, ‘SYS’, ‘SYSTEM’,’LMS’, ‘INDEX_MAINTENANCE’)
order by owner;
spool off

Advertisements