Menu
Ultimately the solution to a ORA-04031 error is adding RAM to shared_pool_size and/or shared_pool_reserved_size. In 11g and beyond , if using AMM, increase memory_max_size to resolve the ORA-04031 error. Cause: More shared memory is needed than was allocated in the shared pool.
An ORA-04031 error message stems from a lack of available System Global Area (SGA) memory space (Note that this differs from Program Global Area (PGA), which correlates similarly with the ORA-04030 error). The error commonly occurs when working with large shared pools of memory.
Free memory Shared pool : SELECT * FROM v$sgastat WHERE name = 'free memory'; News.
Check the value of parameter shared_pool_size: select NAME, VALUE from v$parameter where name='shared_pool_size'; select NAME, VALUE from v$parameter where name='shared_pool_reserved_size'; If the value is lower than 150 MB, increase it to at least 150 MB (400 MB is better), and restart the database.
I need some pointers on how to diagnose and fix this problem. I don't know if this is a simple server setup problem or an application design problem (or both).
Once or twice every few months this Oracle XE database reports ORA-4031 errors. It doesn't point to any particular part of the sga consistently. A recent example is:
ORA-04031: unable to allocate 8208 bytes of shared memory ("large pool","unknown object","sort subheap","sort key")
When this error comes up, if the user keeps refreshing, clicking on different links, they'll generally get more of these kinds of errors at different times, then soon they'll get "404 not found" page errors.
Restarting the database usually resolves the problem for a while, then a month or so later it comes up again, but rarely at the same location in the program (i.e. it doesn't seem linked to any particular portion of code) (the above example error was raised from an Apex page which was sorting 5000+ rows from a table).
I've tried increasing sga_max_size from 140M to 256M and hope this will help things. Of course, I won't know if this has helped since I had to restart the database to change the setting :)
I'm running Oracle XE 10.2.0.1.0 on a Oracle Enterprise Linux 5 box with 512MB of RAM. The server only runs the database, Oracle Apex (v3.1.2) and Apache web server. I installed it with pretty much all default parameters and it's been running quite well for a year or so. Most issues I've been able to resolve myself by tuning the application code; it's not intensively used and isn't a business critical system.
These are some current settings I think may be relevant:
pga_aggregate_target 41,943,040 sga_max_size 268,435,456 sga_target 146,800,640 shared_pool_reserved_size 5,452,595 shared_pool_size 104,857,600 If it's any help here's the current SGA sizes:
Total System Global Area 268435456 bytes Fixed Size 1258392 bytes Variable Size 251661416 bytes Database Buffers 12582912 bytes Redo Buffers 2932736 bytes If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With