I am getting:
Error: ORA-04030: out of process memory when trying to allocate 526592 bytes (PLS non-lib hp,PAR.Carchktb)
when trying to save changes/compile after making changes to a procedure..
I tried to reduce the sort area size int the session didn't help.
Not sure if this helps directly, but it may help us understand the problem...
d) How per session allocations are performed & address space fragmentation
Once the listener has created a users session (thread and stack) the Oracle server code takes over and starts to create the necessary memory structures for the users connection. These allocations and those required by parameters described in section 4.a (which are created as and when the session requires them) are created using the Win32 API call "VirtualAlloc". Each session makes calls to "VirtualAlloc" with the MEM_RESERVE | MEM_COMMIT allocation flags, which both reserves and commits the region of the address space for its private use. For information on how memory is released see section 4.e.
When "VirtualAlloc" is called to reserve memory at a specific address it returns an address that has been rounded down to the next 64K chunk in the virtual address space. Oracle calls "VirtualAlloc" without defining a specific address, by passing in NULL, it is returned the address of the next available 64K chunk. So when a users session makes a PGA, UGA or CGA allocation it is also aligned along the 64K boundaries, committing the requested pages from this boundary up. Users sessions often make many small allocations which are less than 64K, this causes address space fragmentation because many 64K regions exist with only a couple of pages committed.
The following example shows what the address space would look like after the initial allocations have been made by a users session :
The following table can be used as a guideline for calculating the minimum amount of the address space that will be used by a users session. It does not take into account the memory that could be allocated by parameters described in section 4.a as the session proceeds. The default 1Mb stack is also assumed.
Once the address space starts to fill with users session allocations the will be a danger that a new session can not be created due to the lack of available address space. If this occurs the most likely error is :
- ORA-12500 / TNS-12500
- TNS:listener failed to start a dedicated server process
Other possible errors include :
- ORA-12540 / TNS-12540 TNS:internal limit restriction exceeded
- NT-8 Not enough storage is available to process this command
- skgpspawn failed:category = ....
- ORA-27142 could not create new process
- ORA-27143 OS system call failure
- ORA-4030 out of process memory when trying to allocate ....
Due to address space fragmentation and dll's being loaded into the Oracle server processes address space, these errors are likely to occur when the Windows NT performance monitor shows the Oracle process has allocated around
1.6GB / 1.7GB of the 2GB address space. If the 4GT tuning feature is in operation this will be around 2.5GB / 2.7GB. It is important to remember that it is only the committed pages that are backed by physical memory or the page file.