Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server,comp.databases.oracle.tools
Subject: Re: Functions becoming invalid
References: <01bc13cd$ac8fcd20$2d433a82@hallucigenia> <32f9dde6.1733631@news.planet.net>
>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" 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
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: www.arikaplan.com <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page