Re: Functions becoming invalid

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

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

Best of luck,

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> Visit my Web Page: http://homepage.interaccess.com/~akaplan   <->
<->             email: akaplan_at_interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

jared_at_hwai.com (Jared Hecker) writes:

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

>Regards,
>jh
 

>"Glenn Stauffer" <stauffer_at_swarthmore.edu> 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?
>>
>>Thanks,
>>
>>Glenn Stauffer
 

>Jared Hecker, CODBA | Oracle and Sybase Architect and DBA
>jared_at_planet.net | - consulting in the
>76276.740_at_compuserve.com | NYC/NJ region
Received on Thu Feb 06 1997 - 00:00:00 CET