3 Replies Latest reply: Dec 17, 2012 3:42 AM by Udo RSS

    I have an error ora-04031 on XE 11g

    947565
      hello,
      I have an error ora-04031 on XE 11g
      When I restart DB, this error is fixed.
      Can I set any parameter(SHARED_POOL_SIZE) to fixed this error?
      OS:win 2003 server
      ora-00604: error occurred at recursive SQL level 1
      ora-04031: unable to allocate 4064 bytes of shared memory ("shared pool","select t.rowcnt, t.blkcnt, t...","sga heap(1,0)","kglsim heap")
        • 1. Re: I have an error ora-04031 on XE 11g
          Udo
          Hello,
          Can I set any parameter(SHARED_POOL_SIZE) to fixed this error?
          There actually is a parameter, but in 11g XE it's not effective by default because Automatic Memory Management (AMM) is activated. AMM tries to tune the different memory parameters to achieve best performance for all activities in the database.
          You can disable automatic memory management, but I'd not recommend to do that unless you really know what you do.
          In your case, I'd start to investigate if a perticular query is causing that issue and you might be able to tune it, so it needs less memory. A second step would be to check whether your instance is already using the 1 GB you can have in XE. To find out, run the following query
          select * from v$parameter where name in ('memory_target', 'sga_target','sga_max_size','pga_aggregate_target');
          The parameters for AMM would be memory_target and memory_max_target where memory_target is the effecitve maximum amount of memory used by XE. If it's not enabled (set to 0), the other three might be effecitve.
          To increase the value for memory_target you can run
          alter system set memory_target=<size>M;
          where +<size>M+ would be the amount of memory in megabytes. As mentioned before, you can assign a maximum of 1 GB and not more than you've defined in the memory_max_target parameter. To increase that value, you need to run
          alter system set memory_max_target=<size>M scope=spfile;
          and restart your database before you can alter memory_target to a higher value.

          BTW: For further investigation you don't need to restart the database to reset the shared pool: You can do this by issuing the following command
          alter system flush shared_pool;
          If this all doesn't help, I'd recommend to dig in deeper into memory management of the Oracle Database. But let's start with first steps first.

          -Udo
          • 2. Re: I have an error ora-04031 on XE 11g
            947565
            First, I need say thanks to you.
            Secondly, my status as below.

            I already have 1G memory_target.
            Sometimes I also get ora-12518,so I need restart DB.
            when I create DB, I have step by step setup.
            And never to tune any parameter,It's mean my DB had setting default.

            ora-12518 tns listener could not hand off client connection

            NUM     NAME     TYPE     VALUE     DISPLAY_VALUE     ISDEFAULT     ISSES_MODIFIABLE     ISSYS_MODIFIABLE     

            ISINSTANCE_MODIFIABLE     ISMODIFIED     ISADJUSTED     ISDEPRECATED     ISBASIC     

            DESCRIPTION     UPDATE_COMMENT     HASH

            670     memory_target     6     1073741824     1G     FALSE     FALSE     IMMEDIATE     

            TRUE     FALSE     FALSE     FALSE     FALSE     

            Target size of Oracle SGA and PGA memory          (spqce) 1127054141
            • 3. Re: I have an error ora-04031 on XE 11g
              Udo
              I already have 1G memory_target.
              And never to tune any parameter,It's mean my DB had setting default.
              You mean you have 1G without setting that value manually? That sounds awkward, but anyway, since your query shows that the value is effective, it doesn't really matter how it was set.
              Since you say you're on Win 2003: Which exact version do you use and how much physical memory does your machine have?
              Sometimes I also get ora-12518,so I need restart DB.
              This is probably due to the shared pool issue as well, but you could take a look into the database listener's log and the alert log of the database to make sure that this is the actual reason.

              And finally, to bring this up once more: Do you have any idea if this issue comes up regularly in conjunction with a certain application/query?

              -Udo