4 Replies Latest reply: Mar 5, 2013 1:13 AM by Billy~Verreynne RSS

    unable to allocate shared memory

    856913
      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
          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....
            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....
              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
                Billy~Verreynne
                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.