Re: Shared Memory - Pinning a procedure

The unkeep procedure of dbms_shared_pool should remove the lock on the pin for the packaged code; however, it will not remove the code from memory and if the package is in use it will still be pinned into the shared pool. I do not think an unkeep can fail if the call is coded correctly, at least I have never seen the call fail. It is not necessary to unkeep a package before re-compiling it. I take it you are looking at the kept column of v$db_object_cache to see if the keep/unkeep worked and not at the pins column.

In order to compile a new version of the package your session must be able to obtain a lock (technically, I believe it is a latch) on the package. If you are able to do this then Oracle will replace the stored version with the new version providing the compile is successful. The next execution of the package will be of the new code. Your recompile of valid code can fail due to failure to obtain enough contiguous free space in the shared pool to hold the entire compiled object.

I hope this helps.

Jay <gjay_at_erols.com> wrote in article <70qid4$pnn$1_at_autumn.news.rcn.net>...
> What happens?
> When a stored procedure is pinned in ORACLE Shared Pool
> by execute dbms_shared_pool.keep('ProcedureName')
> and it is not unpinned by a statement UNKEEP
>
> Subsequently if a new segment is added to the Stored Procedure and
executed.
>
> What happens to the pinned code? and the new code?
>
> Any details can be appreciated.
>
> Thanks.
>
>
>
>
Received on Mon Oct 26 1998 - 14:17:27 CST