Re: PL*SQL Procedures becoming corrupt / becoming uncompiled

This occurs because they are validated each time they are called. The problem usually occurs most frequently when procedures call each other, this can result in deadlock on the datadictionary. If they are really heavily used you should pin them in the shared pool. At least this prevents revalidation.
Use dbms_shared_pool.keep('<USERNAME>.<PROCEDURENAME>')

Invalid procedures are recompiled automatically. Alternatively you could try
dbms_utility.compile_schema('<SCHEMANAME>');

Hth,

Sybrand Bakker, Oracle DBA

<catatony_at_my-deja.com> wrote in message news:8ar5pf$sgc$1_at_nnrp1.deja.com...
> Several times per week, several of the procedures stored in my
> production database are becoming corrupt / becoming uncompiled. The
> procedures affected are mostly select only, and most of them are heavily
> used. My only workaround at this point is to manually recompile the
> procedures. This is a poor workaround as some user must experience a
> problem before I become aware of the problem.
> What could be causing the procedures to become corrupt? Is there
> potentially a shared pool problem? Is there any way to get Oracle to
> automatically recompile corrupted procedures?
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Mar 16 2000 - 00:00:00 CST