Re: ORA-04031: unable to allocate 4096 bytes of shared memory

Hi! Bruno:

General speaking, the shared_pool_size could be set to 15 ~ 20% of real memory (for oracle). Make sure your SGA not over the real memory.

The pinning objects in the shared pool following these steps:

1st. create DBMS_SHARED_POOL PACKAGE by run two sql script (rdbms/admin)
2nd. flush the shared pool
3nd. restart instance, so it can be read into shared pool
4th. pin it (type is: P for procedure, C for cursor and R for trigger)

Svrmgr> @dbmspool
Svrmgr> @prvtpool

ALTER SYSTEM FLUSH SHARED POOL; resatrt your instance here

exec DBMS_SHARED_POOL.KEEP(your_object, type)

hope it is help.
JC

Bruno Paquette <bpaquette_at_airtransat.com> wrote in message news:aNFp3.39400$Q4.78476_at_wagner.videotron.net...
> Hi all,
>
> First, I am using Oracle 7.3.2.3
>
> I have create some stored procedures for some system and I have like 5
> packages with bodies, 6-7 functions and 6-7 procedures.
>
> When I execute my "main" procedure which calls all 5 packages and
> functions/procedures related to it, I receive this message saying that I
> don't have enough shared memory since it`s unable to allocate 4096 bytes.
>
> My shared_pool_size parameters is set to 6000000.
> Is it enough ?
>
> In the Oracle 7 server messages it says that more shared memory is needed
> than was allocated in the operathing system (Unix, Solaris) process. SGA
> private memory has been exhausted.
>
> The actions proposed are :
> Using the DBMS_SHARED_POOL package to pin large package
> But how ? :)
>
> Reduce your use of shared memory
> humm.. how ?
>
> Increase the value set for the SHARED_POOL_SIZE initialization parameter
> I have tried to set it to 9000000 but the Database won`t restart
saying
> that something doesn`t work.
>
> We don`t have DBA here so it`s like do what you can to make it work but
make
> it work..
>
> Any tips ?
>
> Thanks a lot,
>
> Bruno Paquette
> bpaquette_at_airtransat.com
>
>
>
Received on Thu Aug 05 1999 - 16:04:17 CDT