ORA-4030 as a result of 1GB limit of memory per process (Doc ID 272786.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 9.2.0.5 - Release: 9.2 to 9.2
Information in this document applies to any platform.

Symptoms

Running query which requires a large sort area memory. The PGA grows until the 1GB sort_area_size is used and then errors out with ORA-04030, although the instance has much more than 1GB TEMP space available that is never touched.

You can hit this error under following scenarios:

Case - I
-------------

Set workarea_size_policy=manual
and
sort_area_size > 1GB

Case - II
--------------

Set workarea_size_policy=auto
and
pga_aggregate_target is set to a large value, so that a single process uses more than 1 GB of process memory.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms