search cancel

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

book

Article ID: 174004

calendar_today

Updated On:

Products

Symantec Products

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

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.

Environment

Oracle 12c 

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