Subject: Re: Functions becoming invalid
References: <01bc13cd$ac8fcd20$2d433a82@hallucigenia> <>

>Normally stored procedures and functions become invalid only when they are
>dependent on another object that has been recompiled - this was one of the
>reasons for the introduction of packages, btw.

>If your system is stable and this is happening I would call Oracle support,
>you may have a corruption in your data dictionary.


>"Glenn Stauffer"  wrote:

>>I administer a system which makes heavy use of stored functions and one in
>>particular is required for several key reporting views.  For some reason,
>>this function becomes invalid periodically and almost always after the
>>database is shutdown for backup and restarted.  Can someone advise me as to
>>why database objects and particularly functions become invalid and what
>>might cause a function to attain this state fairly frequently?
>>Glenn Stauffer

Stored functions and procedures can also become invalid when the table(s)
that they depend on get dropped. Check to see if users are dropping and
re-creating tables. Sometimes batch processes or data loading processes
drop and re-create tables. If so, your functions/procedures will become

To help you narrow your search, look at the ALL_DEPENDENCIES table to find
out what objects (and their types) depend on the functions that get

Best of luck,

-Ari Kaplan
Independent Oracle DBA Consultant

<-> Visit my Web Page:                          <->
<->             email:                    <->

Back to Ari Kaplan's Home Page /index.htm">Back to Ari Kaplan's Home Page