[20230320]ORA-00855 PGA_AGGREGATE_TARGET cannot be set because of insufficient p

[20230320]ORA-00855 PGA_AGGREGATE_TARGET cannot be set because of insufficient physical memory.txt --//http://mvelikikh.blogspot.com/2023/03/ora-00855-pgaaggregatetarget-cannot-be.html链接说明在启动数据库后设置 --//pga_aggregate_target很大遇到的情况.给出了这样的情况pga_aggregate_target设置的最大值.重复测试看看. 1.环境： SYS@192.168.100.237:1521/orcldg> @ pr ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 19.0.0.0.0 BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. SYS@192.168.100.237:1521/orcldg> sho parameter ga%target PARAMETER_NAME       TYPE        VALUE -------------------- ----------- ------- pga_aggregate_target big integer 4G sga_target           big integer 7504M $ grep Mem /proc/meminfo MemTotal:       16129872 kB ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ MemFree:          632896 kB MemAvailable:    6159872 kB --//16129872*1024 = 16516988928 --//16129872*1024 = 0x3d87d4000 (gdb) printf "0x%lx\n", (long) ksmc_physmem_pga_target(0x3d87d4000) 0xffffffffd0852999 (gdb) printf "0x%x\n", (long) ksmc_physmem_pga_target(0x3d87d4000) 0xd0852999 SYS@orcldg> oradebug setmypid Statement processed. SYS@orcldg>  oradebug call ksmc_physmem_pga_target 0x3d87d4000 Function returned D0852999 --//D0852999 = 3498387865 --//3498387865/1024/1024/1024 = 3.25812759343534708023G. --//PGA_AGGREGATE_TARGET  最大设置 3498387865 --//计算公式如下： max PAT = (TotalMemory * _pga_limit_physmem_perc / 100 - SGA_TARGET) * 100 / _pga_limit_target_perc _pga_limit_physmem_perc: the parameter limiting total PGA and SGA (90% by default, in other words Oracle reserves 10% for OS and everything else): _pga_limit_target_perc: the default percent of PAT for pga_aggregate_limit (200% by default): SYS@192.168.100.237:1521/orcldg> @ hide _pga_limit_physmem_perc NAME                    DESCRIPTION                                                        DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD ----------------------- ------------------------------------------------------------------ ------------- ------------- ------------ ----- --------- _pga_limit_physmem_perc default percent of physical memory for pga_aggregate_limit and SGA TRUE          90            90           FALSE IMMEDIATE SYS@192.168.100.237:1521/orcldg> @ hide _pga_limit_target_perc NAME                    DESCRIPTION                                                        DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD ----------------------- ------------------------------------------------------------------ ------------- ------------- ------------ ----- --------- _pga_limit_target_perc  default percent of pga_aggregate_target for pga_aggregate_limit    TRUE          200           200          FALSE IMMEDIATE max PAT = (TotalMemory * _pga_limit_physmem_perc / 100 - SGA_TARGET) * 100 / _pga_limit_target_perc         =  (16129872*1024*90/100-7504*1024*1024 ) *100/200 = 3498387865.6 --//3498387865.6与调用ksmc_physmem_pga_target函数的结果接近. --//而实际上我的设置是pga_aggregate_target=4G.大于3498387865.6. SYS@192.168.100.237:1521/orcldg> show spparameter pga_aggregate_target SID      NAME                 TYPE        VALUE -------- -------------------- ----------- ------ *        pga_aggregate_target big integer 4G SYS@192.168.100.237:1521/orcldg> alter system set pga_aggregate_target=4G scope=memory; alter system set pga_aggregate_target=4G scope=memory * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-00855: PGA_AGGREGATE_TARGET cannot be set because of insufficient physical memory. SYS@192.168.100.237:1521/orcldg> alter system set pga_aggregate_target=3498387866 scope=memory; alter system set pga_aggregate_target=3498387866 scope=memory * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-00855: PGA_AGGREGATE_TARGET cannot be set because of insufficient physical memory. SYS@192.168.100.237:1521/orcldg> alter system set pga_aggregate_target=3498387865 scope=memory; System altered. --//原始链接有一段话: --//http://mvelikikh.blogspot.com/2023/03/ora-00855-pgaaggregatetarget-cannot-be.html Please note that this formula applies when the value is set while the instance is up and running. It is still possible to set a higher value in spfile and bounce the instance. 请注意，当实例启动和运行时设置值时，适用此公式。仍然可以在spfile中设置一个更高的值并弹跳实例。