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

Question

I came across ORA-04036 (on 12c) while one of my team members was creating an oracle text index.

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
ORA-00039: error during periodic action
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366

Answer 1

in my case, It failed due to a very high parallel degree 48 (requiring too many session and increased PGA) while creating an index (or any other parallel operation). We should try to reduce parallelism to avoid this. I tried with DOP 8 script ran successfully.

In Oracle Database 12c, a new parameter called PGA_AGGREGATE_LIMIT sets a hard limit on the amount of PGA taken up by an instance. When the overall PGA occupied by all the sessions in that instance exceed the limit, Oracle kills the session holding the most untunable PGA memory, releasing all PGA memory held by that session. That killed session gets this message.

Note the new Oracle error ORA-4036. This is an extremely useful feature to control runaway PGA consumption by sessions issuing untuned queries. The parameter pga_aggregate_limit, if not explicitly defined, defaults to the greater of these three numbers: 2 GB 3 MB times the value of the processes parameter 2 times the value of the parameter pga_aggregate_target But this limit does not exceed the 1.2 times the total physical memory after taking away the total SGA size. Don’t like this new feature and want to go back to the previous (pre-12c) behavior? Sure; just set the value of pga_aggregate_limit to 0 and the parameter will not have any impact.

Posting answer in the interest of community readers as i could not find it on SO yet. Pls see this for further reading...