Re: ORA-4031 Error

Had this problem with one of our databases, and increasing the shared_pool_size didn't solve the problem... it just delayed it's occurance a little longer. Pinning packages AND tuning shared_pool_reserved_size eliminated the problem. I also modified dbstart to pin the packages at startup time, at a minimum I always pin these:

     execute sys.dbms_shared_pool.keep('SYS.STANDARD');
     execute sys.dbms_shared_pool.keep('SYS.DBMS_APPLICATION_INFO');
     execute sys.dbms_shared_pool.keep('SYS.DBMS_DESCRIBE');
     execute sys.dbms_shared_pool.keep('SYS.DBMS_STANDARD');
     execute sys.dbms_shared_pool.keep('SYS.DBMS_OUTPUT');
     execute sys.dbms_shared_pool.keep('SYS.DBMS_UTILITY');

When pinning packages not owned by SYS, I believe that PUBLIC needs execute grants, otherwise the package won't be kept.

To see how much free memory the shared_pool has:

SQL> select * from v$sgastat where name = 'free memory' ;

POOL        NAME                                BYTES
----------- ------------------------------ ----------
shared pool free memory                      89137828

Suzy

pschauss_at_parker.com wrote:
>
> One of our application processes got the following error:
>
> ORA-04031: unable to allocate 4216 bytes of shared
> memory ("shared pool","unknown object","sga heap","library cache")
>
> The Error Messages manual says that the cure it either to use
> dbms_shared_pool package to pin large packages into memory,
> reduce use of shared memory, or increase the value of the SHARED_POOL_SIZE
> parameter.
>
> If we opt for the latter, how do we know how much larger to make it?
>
> Is there some system table or view which will tell give us a
> high water mark on use of shared pool so we can tell how much room
> we've got once we increase this parameter?
>
> The code which caused the error was a SET TRANSACTION READ ONLY
> statement in PRO*C. How was this putting us over the limit
> on our shared pool usage?
>
> thanks,
> Peter Schauss
> Parker Hannifin Corp
> Smithtown, NY
>
> --
> Author:
> INET: pschauss_at_parker.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Thu Jun 15 2000 - 14:09:34 CDT