ORA-06580: Hash Join ran out of memory while keeping large rows in memory

Products

• Data Loss Prevention Oracle Standard Edition 2

Issue/Introduction

When running URT we see the following messages ORA-06580: Hash Join ran out of memory while keeping large rows in memory

ORA-06580: Hash Join ran out of memory while keeping large rows in memory

Environment

Oracle 12c 

Cause

setting PGA memory management to Manual mode will lead to ORA-06580: Hash Join ran out of memory while keeping large rows in memory.
In such cases following parameters WORKAREA_SIZE_POLICY will be manual and PGA_AGGREGATE_TARGET will be zero.

Resolution

Above oracle 9i avoid manual PGA management and use automatic mode.
 
Steps:
 
Edit spfile and set the parameters by logging as SYSDBA as follows:

1. sqlplus /nolog
2. conn sys as sysdba;
3. alter system set PGA_AGGREGATE_TARGET=<see note below> scope=BOTH;
4. alter system set WORKAREA_SIZE_POLICY=AUTO scope=BOTH;
5. Re run URT

 
NOTE: PGA_AGGREGATE_TARGET=300M - or any other bigger value