Oracle on 32-bit Windows: ORA-4030 errors

Over the last year I have seen a number of customers that have problems with Oracle on windows. They are getting frequently an ORA-4030 error. Now Oracle on windows is implemented differently than Oracle on UNIX. The big difference is that Oracle on Windows shares one process with all clients (threads in this case). This process has a limited process space, which is by default 2GB and can be enlarged to 3.5GB. So this process space has to accommodate all the Oracle sessions (threads). So if you have a quick look at the Process space it basically consists of 4 parts (there are probably more, but I am not an Windows geek/expert):

The Oracle Executable

The Oracle SGA

The Heap (for all threads/sessions)

The Stack

This has a number of implications. You can’t really tune the SGA to a very large size; you can only support a certain number of users; the heap can easily get fragmented and that means that large allocations will fail and there are other related problems.

So what can you really do to make this work? Reduce the SGA_MAX_SIZE or even remove the parameter and rely on your db_block_buffers and shared_pool_size settings. Or you can reduce the number of sessions/threads that connect to Oracle. But the your best bet is to reduce the amount of memory needed for the SGA. Another option is to switch to 64 bit windows and 64 bit windows, that will also allow for a larger process space.

The limit of the 3.5GB instead of the 4GB seems to be from the fact that some memory is being used for Windows to map OS related libraries and areas into the process space.

PAE seems and sounds like a good idea . There is one problem with it however if you use it with Oracle. You can only use it to store the Oracle Buffer Cache (if it grows beyond a certain size). You can’t use it for the shared pool for example.

You can also reduce the memory footprint by changing the (per thread!) stack sizes or process heap size. Oracle used to pack utilities like orastack and oraheap.exe with the distribution..I dunno if they are still there however. We used orastack a lot during the good old Baan days, remember? 🙂 That we we could assure we could create enough sessions..