Sunday, March 20, 2011

Finding Invalid Objects in database. Nice Command



SELECT 'ALTER '|| OBJECT_TYPE || ' '|| OBJECT_NAME || ' COMPILE;'

FROM ALL_OBJECTS WHERE OBJECT_NAME IN (

select d.name

from sys.obj$ d, sys.dependency$ dep, sys.obj$ p

where d.obj# = dep.d_obj# and p.obj# = dep.p_obj#

and d.status = 1

and bitand(dep.property, 1) = 1

and d.subname is null

and not(p.type# = 32 and d.type# = 1)

and not(p.type# = 29 and d.type# = 5)

and not(p.type# in(5, 13) and d.type# in (2, 55))

and (p.status not in (1, 2, 4) or p.stime != dep.p_timestamp));

No comments:

Post a Comment