ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

Error Message

When creating an Oracle spatial index on a dataset stored in SDO_Geometry such as in this example:

CREATE INDEX "SPATIAL_EDIT"."A422_IX1" ON "SPATIAL"."POLY" ("SHAPE")
INDEXTYPE IS "MDSYS"."SPATIAL_INDEX"

The following error occurs:

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

Cause

ORA-04036 error occurs when private memory across the instance exceeded the limit specified in the PGA_AGGREGATE_LIMIT initialization parameter.
The largest sessions using Program Global Area (PGA) memory were interrupted to get under the limit.

With spatial data stored in SDO_Geometry, error can occur with very large datasets with features that vary significantly in size. 

Solution or Workaround

To avoid the ORA-04036 error and termination of processes:

• Set the PGA_AGGREGATE_LIMIT = 0

OR

• Increase the PGA_AGGREGATE_LIMIT initialization parameter

More details can be found on Oracle's support page (Oracle log-in required)