Re: Reg: ORA-04030

On Windows, Oracle runs in a single service called oracle.exe. This service contains multiple threads that represent the background and user processes. Because Oracle is running as a single service, all of the threads together can only address 2GB of physical memory, which is the limit for a 32-bit operating system.
On a 32-bit UNIX or Linux platform, the SGA is still subject to the 2GB limit, but the user processes run in their own process space and may address their own memory.
Your error is caused by the 32-bit, 2GB memory limitation; even though your SGA may be smaller than 2GB, every session started against the database chips away at the addressable memory for the oracle.exe service. When the total memory addressed hits 2GB, you will not be allowed to start another session and you will see ORA-04030. 

There are a few ways to combat this problem:
1. Don't run so many user sessions. This is not always practical or possible. 2. There are ways to increase the memory limit on 32-bit Windows to up to 4GB. Check out Oracle's platform guide for 32-bit Windows for more direction: http://download.oracle.com/docs/cd/B19306_01/win.102/b14304/architec.htm 
3.  Migrate to 64-bit Windows.
4.  Migrate to UNIX or Linux for your database OS.
If you are committed to Oracle on the Windows platform and cannot practically limit the number of user sessions or substantially shrinking your SGA, explore option 2, then option 3, in that order.
You might check out Metalink article 373602.1 as well. It references other related articles you might find helpful. 

Hope that helps.

Chuck Edwards
Blue Gecko, Inc.
http://www.bluegecko.net


On Feb 24, 2009, at 4:28 PM, NEELI-SC, Mamta wrote:


HI Chuck,

Yes it’s 32-bit Windows.

Regards,
Mamta

Other related posts: