3 Replies Latest reply: Sep 22, 2010 5:27 PM by Philippe Florent RSS

    ORA-04031  unable to allocate

    Nilton
      Oracle 10.2.0.4

      Sun Solaris

      Hi

      We are facing for the last few days this ORA-04031 Errors. All jobs keep on failing on this particular database.

      ORA-04031: unable to allocate 35782 bytes of shared memory ("shared
      pool","unknown object","sga heap(1,1)","KTI SGA freeable small pool")

      This particular server has about 8 databases running. But We are facing this issue only on this server.

      Whenever faced this issue, I just flush the shared pool

      alter system flush shared_pool;

      Then everything works fine. But only for the next few hours. Then again, 4031 error.

      The SGA_TARGET is set for 180 MB. SGA_MAX_SIZE is also the same.

      Shared pool free memory is as follows

      shared pool free memory 13122512



      What is the permanent solution for this kind of error ?
        • 1. Re: ORA-04031  unable to allocate
          dba05
          Try to increase sga_target.

          180M seems to be very less.Keep at least 512M.

          Thanks...
          • 2. Re: ORA-04031  unable to allocate
            BelMan
            Hi,

            what is your platform 64 bit or 32 bit?

            How many RAM do you have?

            ORA-04031: unable to allocate 35782 bytes of shared memory ("shared
            pool","unknown object","sga heap(1,1)","KTI SGA freeable small pool")

            It seems like you have a complex select statement
            • 3. Re: ORA-04031  unable to allocate
              Philippe Florent
              Hi,
              8 instances ? If it is a production server it is not really a good situation and even if it is often difficult you could think of consolidating the databases.
              Read that from Tom Kyte : http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4347545437371
              You can increase the SGA as suggested but perhaps things will go worse for the other instances since you don't know anything about the situation. Perhaps this server is swapping for example... Did your SA indicate you how much memory you can allocate to Oracle ?
              Do you have a trace etc. to know which statement causes the problem ?
              Best regards
              Phil