Wednesday, November 10, 2010

Manually Recompile Objects

Sometimes one needs to manually recompile objects in the database. The query below generates the commands required. It does however not take care of any sequencing that might be required.

I have posted tips for "automatic" re-compilation here.

select 'alter '||
case when object_type = 'PACKAGE BODY' then 'PACKAGE'
when object_type = 'TYPE BODY' then 'TYPE'
when (object_type = 'SYNONYM' and owner = 'PUBLIC') then 'PUBLIC SYNONYM'
else object_type
end||' '||
case when (object_type = 'SYNONYM' and owner = 'PUBLIC') then null
else owner||'.'
object_name||' compile'||
case when object_type in ('PACKAGE BODY','TYPE BODY') then ' BODY'
else null
from dba_objects
where status != 'VALID'
and owner like 'T136291%'

No comments: