ORA-06580: Hash Join ran out of memory while keeping large rows in memory
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

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

Any version of Oracle

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."

Additionally, the parameter WORKAREA_SIZE_POLICY will be set to "manual" and PGA_AGGREGATE_TARGET=0.

Resolution

Above Oracle 9i, avoid manual PGA management and use Automatic mode.
 
Steps:
 
Set the parameters by logging in as SYSDBA and editing 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. exit;
  6. Re-run URT

 
NOTE: PGA_AGGREGATE_TARGET=300M - or any other bigger value as determined by DBA
 
 

Additional Information

Setting scope to BOTH will edit the spfile as well as the current instance.