Re: PL*SQL Procedures becoming corrupt / becoming uncompiled

After a few fits and starts, I have successfully pinned several packages in the shared pool using dbms_shared_pool.keep. At this point, I just have to wait for the packages to become "invalid", and then determine if the pinned packages are immune. Thanks for your help. A couple of questions in the meantime: 1) Do I need to re-pin the packages every time I restart the database? If so, how do I do so? 2) Do you know of any way to verify which procedures are currently pinned?

NOTE: The "fits and starts" I refer to above were due to an error I made when loading dbms_shared_pool into the database. Basically, I ran the $ORACLE_HOME/rdbms/admin/dbmspool.sql script as user 'system' rather than as 'sys' as is required. In shuffling around, trying to get the package to compile, I ran the script from svrmgrl as user 'internal'. The result was duplicate object references for users 'sys' and 'system', which causes Oracle a bit of heartburn. I have since discovered that Oracle recommends that basically every script in $ORACLE_HOME/rdbms/admin be run as user 'sys'. At any rate, for more information about this situation, see oracle notes 2077829.6 & 1030426.6 (or contact me).

In article <953228903.12904.0.pluto.d4ee154e_at_news.demon.nl>, "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> 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
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Mar 17 2000 - 00:00:00 CST