Quote:
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.
regards