This discussion is archived
3 Replies Latest reply: Dec 17, 2012 1:42 AM by Udo RSS

I have an error ora-04031 on XE 11g

947565 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

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