Oracle updatexml throws pga memory on high volume data

Question

I’m using the below updatexml function to update an Xmltype column scrambling for many tables using procedure. Some tables have huge volume of data for those update is failed with PGA memory issue Command as follows:

Update table_name
set XMLRECORD = updatexml(xmlrecord,'/row/c1/text()','SCRAMBLE1','/row/c3/text()','SCRAMBLE2')

Error message:

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 04036.00000 - “PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT” *Cause: Private memory across the instance exceeded the limit specified in the PGA_AGGREGATE_LIMIT initialization parameter. The largest session using Program Global Area(PGA) memory were interrupted to get under the limit. *Action: Increase the PGA_AGRREGATE_LIMIT initialization parameter or reduce memory usage.

We tried to increase the pga limit but still the issue occurs. Appreciate any suggestion to handle this.

Answer 1

I would just recreate the table with the new data.