ORA 4036 while creating Index
A reader, August 16, 2018 - 1:12 pm UTC
Hi,
I am trying to create an index on a big table. The table has almost billion rows. Now my index creation fails with -
Oracle Error: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
There is nothing else except the index creation in progress. I may have to drill down further but my question is -
Can creation of an (B+ Tree) index in oracle lead to a PGA overflow?
Regards!!
August 18, 2018 - 1:08 pm UTC
Yeah, typically you should not be getting that - pga_agg_limit is designed to make sure that you don't smoke the entire server (assuming its been set sensibly).
But sorting operations (such as create index) should respect that and dump to temporary storage accordingly.
It is possible to disable the check by setting the limit to zero (which means we wont keep an eye on what we're doing to your server!), but I'd log a call with Support before heading down that path
Thanks!!
A reader, August 20, 2018 - 4:15 pm UTC
Thanks Chris!! This confirms what I thought :)