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.
I will update later with some more info.