Troubleshooting: auth.product pipeline causes Oracle PGA limit error

When using Oracle database Versions 12c and up and the eSite indexing model, the auth.product pipeline can cause an ORA-04036:exceeds PGA_AGGREGATE_LIMIT after an otherwise successful full reindexing. In a common scenario you

1. Update the attribute dictionary Catalog > Extended Site Catalog Assset Store > Attribute Dictionary in Management Center, changing the SwatColor property to facetable.
2. Execute a Near Real Time update.
3. Poll the auth.product pipeline, with for instance:

   https://server:30801/connectors/auth.product/runs?size=1000&orderDate=desc

,

This scenario can return an error message such as

{
"run": "n-e3031ee8-a11c-4dad-901e-da6e366f2689",
"date": "2023-11-01T20:57:13.089Z",
"changeHistoryEventId": "Not exists"

You can find further details by making a query based on the runId returned above:

https://server:30801/connectors/auth.product/runs/n-e3031ee8-a11c-4dad-901e-da6e366f2689?size=1000&type=summary&type=trace&type=log&logSeverity=I&logSeverity=E&logSeverity=T&orderDate=asc

The returned details include the following error message:

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

There is an optional parameter, scope that you can add to this system command. The scope parameter can have these values:

MEMORY

The present change is retained in memory only for the current session or at the system level for the current run of the system (if used with the ALTER SYSTEM command). The change will be lost upon restart of the database instance.

SPFILE

The change is saved in the server parameter file but not retained in memory for this session. It will take affect on subsequent database restarts.

BOTH

Performs both of the above actions. The change is added to this session's memory, and saved to the server parameter file so that it also takes effect in subsequent sessions.