Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2181
  • Last Modified:

ORA-04030 out of process memory

Hi,
I am getting the error: ORA-04030 out of process memory when trying to allocate 12750 bytes (hash-join,subh,kllcqas,kllsltba)

when running a PLSQL script. any ideas? its on a fairly well specced machine - with 1GB ram allocated to the database instance, plus the machine has ample swap file (which i tried increasing and still got same error). Its oracle 9i r2.

the scritp is a fairly standard sort of thing - process a bunch of records from a table (theres about 500000+ records in the table) one by one.
0
mwoolger
Asked:
mwoolger
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Please check in metalink.oracle.com the following note:
Note:116076.1   Tackling ORA-4030 on WindowsNT
This should give you some input about how to solve this problem

another hint:
Do you got HASH_AREA_SIZE defined in initora ?
As default it's 2*SORT_AREA_SIZE.

If you do not have HASH_JOIN_ENABLED=FALSE, optimizer prefers it over NESTED LOOP or SORT-MERGE when joining tables. So hash join uses HASH_AREA_SIZE to store information from table 1 and if it's not enough, then Oracle uses TEMP-file. Then it's checking join condition values from table 2 against those stored values.

You may want to reduce/set the value of HASH_AREA_SIZE, so that your system is not out of memory.

Hope this helps
CHeers
0
 
BobMcCommented:
What version of Oracle are you using?
I used to get a lot of these types of errors from 8.1.7.0 -> 8.1.7.2.5 due mainly to bugs.

I had to patch up to 8.1.7.4.x to solve them.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now