Hi @Kalana Rathnayake,

What are the values set for pga_aggregate_limit and pga_aggregate_target and processes parameters in the database?

You can run following statement in a command window logged in as SYS to find out the current value set for this parameter:

SHOW PARAMETER <parameter_name>

Hi Kalana,

If  you have this error in IFS internal environments, 
Set PGA_AGGREGATE_LIMIT to 0 , which removes the limit on PGA usage per session ( then parameter will not have any impact.).

alter system set pga_aggregate_limit=0 scope=both;

more information available here 

https://oracle-base.com/articles/12c/pga_aggregate_limit_12cR1

I’m not an expert in this area, but  with reference to LCS cases G1792550,G2003622) , I found below information.

“IFS do not recommend any value for PGA_AGGREGATE_LIMIT. Oracle introduced it in later versions. so, as I mentioned, Please make it to 0 so that it will only use PGA_AGGREGATE_TARGET. then you will only need to increase PGA_AGGREGATE_TARGET depending on your client-server connection is.”

Also you can refer below article as well.

hope this will help.

Hi Manoj, 

Thanks for this valuable information. 

I’ll try this way to alter this PGA_AGGREGATE_LIMIT to 0. 

Best Regards,
Kalana

SHOW PARAMETER <parameter_name>

Hi Charith, 

Thanks for your answer .

It is listed as follows, 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 2G
pga_aggregate_target                 big integer 868M

Best Regards,
Kalana

Hi @Kalana Rathnayake,

If this is an internal environment, as suggested by Manoj, you can set the pga_aggregate_limit to 0 and try again. According to Oracle Documentation, this is a dynamic parameter, so you don’t need to bounce the database after setting the value.

When pga_aggregate_target is set to a particular value, Oracle would try to honor that by automatically allocating memory as necessary. But pga_aggregate_limit sets a hard limit on how much memory can be allocated. Setting it to 0 would remove that limit.

If the customer runs into the same issue however, it would be best to advise them to acquire the help of an Oracle DBA as tuning these parameters should be done by someone with expertise, taking many other factors into account. For customers who have Oracle ASFU support through IFS, you can point to the following documentation from Oracle regarding tuning the Program Global Area:

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/tuning-program-global-area.html#GUID-FE1954FB-FAC9-4302-896E-6F47EB6C8A73

IFS recommends automatic PGA memory management, which is documented here:

https://docs.ifs.com/techdocs/foundation1/020_installation/020_installing_fresh_system/010_planning_installation/010_storage_tier_considerations/010_oracle_db_19c/default.htm#Automatic_PGA_Memory_Management

Hope this helps!

Hi All,

I used to report to CoS to set PGA_AGGREGATE_LIMIT to 0 whenever the error is received, but having read the above feedback, it sounds like you can set the DB parameter to 0 in internal IFS environments.
Thank you for the information.

Sobha Korottage