当前位置: 代码迷 >> Oracle管理 >> 紧急ora-04031:unable to allocate 27996 bytes of share memory
  详细解决方案

紧急ora-04031:unable to allocate 27996 bytes of share memory

热度:504   发布时间:2016-04-24 05:54:06.0
紧急求助:ora-04031:unable to allocate 27996 bytes of share memory
紧急求助:ora-04031:unable to allocate 27996 bytes of share memory 我用的是10G

------解决方案--------------------
You should increase your shared pool size. You can also flush shared pool to avoid this error temporarily.


If the shared pool is out of memory, either use the dbms_shared_pool package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the INIT.ORA parameters "shared_pool_reserved_size" and "shared_pool_size". If the large pool is out of memory, increase the INIT.ORA parameter "large_pool_size".

------解决方案--------------------
flush sga...


同楼上的结论, 可能是你的SGA碎片化严重,最有效的解决办法应该是修改参数, 不能重启数据库的话就flush sga 了。
------解决方案--------------------
查看数据库的配置参数是否正确,尤其是shared_pool_size,
或者通过如下sql确认:
select pool,sum(bytes) from v$sgastat group by pool;
如果设置正确,不排除遇到oracle bug的可能。


引用楼主 linxi 的帖子:
紧急求助:ora-04031:unable to allocate 27996 bytes of share memory 我用的是10G

------解决方案--------------------
你可以在sqlplus中:show pool看看这些内存值的大小。

10G的话最好启用ASMM,让oracle自动管理内存,分配SGA的大小。
只需设置:
alter system set sga_target=300m scope=spfile; --设置SGA的目标值为300mb
alter system set sga_max_size=300m scope=spfile; --设置SGA的最大值为300mb
再:
alter system set shared_pool_size=0;
还有这些SGA组件也可自动管理:db_cache_size,large_pool_size, java_pool_size。

最后重启实例后生效。

  相关解决方案