This discussion is archived
4 Replies Latest reply: Mar 4, 2013 11:13 PM by BillyVerreynne RSS

unable to allocate shared memory

856913 Newbie
Currently Being Moderated
while connect database via appllication i received the following error herewith i send the sizes

ora-04031:unable to allocate 184 bytes of shared memory("shared pool") ORA:02063

Data Base Utility : ORA-04031: unable to allocate 48 bytes of shared memory ("shared pool","BEGIN PS_PK_global_variables...","parameters","kglpda")

sga_max_size  232M

shared_pool_size  164M

pga_aggregate_target  45M

can anyone advice on this
  • 1. Re: unable to allocate shared memory
    moreajays Pro
    Currently Being Moderated
    Hi,

    If its 10g and above make sure sga_target is set to non-zero (or 232 M in this case) then set shared_pool_size=0 followed by restart of the instance



    Thanks,
    Ajay More
    http://www.moreajays.com
  • 2. Re: unable to allocate shared memory
    Aman.... Oracle ACE
    Currently Being Moderated
    This is a memory fragmentation error in the shared pool. What is happening is that you are not able to find 48 bytes of memory chunk contigeously in the shared pool and it's throwing the error. I don't think you need to go to SGA_TARGET( it won't hurt though to use it) but you would need to check that why even such small memory is not available for you in the shared pool? There is a metalink document which would help, MOS 127638.1 . And you may want to include Oracle support to help you in it too.

    Aman....
  • 3. Re: unable to allocate shared memory
    Aman.... Oracle ACE
    Currently Being Moderated
    moreajays wrote:
    Hi,

    If its 10g and above make sure sga_target is set to non-zero (or 232 M in this case) then set shared_pool_size=0 followed by restart of the instance


    Why to restart the instance? SGA_TARGET can be changed dynamically.
    http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams233.htm#REFRN10256

    Aman....
  • 4. Re: unable to allocate shared memory
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    853910 wrote:
    while connect database via appllication i received the following error herewith i send the sizes
    ora-04031:unable to allocate 184 bytes of shared memory("shared pool") ORA:02063
    In my experience, this error is typical of clients creating non-shareable cursors (i.e. parsing and executing SQLs without using bind variables).

    If this is the case, then bumping up the shared pool size is simply moving the brick wall a few metres further away in order to run even faster into it.

    I always check for a lack of shareable cursors first in a case like this. There are postings on AskTom, support notes on Metalink/Oracle Support, and threads on this forum, dealing with the issues around not using bind variables and running into ORA-4031 errors.

    I suggest you point your web browser at your favourite search engine and research this issue.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points