我们公司的ORACLE数据库经常报如下错误(硬件环境是IBM X225。ORCLE8.1,UNIX5.05),在2个月中此问题发生了近十次,严重影响了公司的正常运行。有哪位高手能指点一下)
此错误是在TOPAD的工具中看到的he following error has occurred:
ORA-04031:unable to allocate 4096 bytes of shared memory[''shared pool'',''unknown object',''PL/SQL MPCODE'',''BAMIMA:Bam Buffer'']
此错误是在TOPAD的工具中看到的he following error has occurred:
ORA-04031:unable to allocate 4096 bytes of shared memory[''shared pool'',''unknown object',''PL/SQL MPCODE'',''BAMIMA:Bam Buffer'']
system process. SGA private memory has been exhausted
Action: 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 set for the SHARED_POOL_SIZE
initialization parameter.
调节INIT中的SGA参数来看看了。
ORA-04031:unable to allocate 4096 bytes of shared
-------------------------------------------------
原因很多:
1. SGA中shared_pool_size 容量偏小;
2. 应用端存在不合理的代码
3. Oracle本身的Bug
1.shutdown数据库
2.修改init文件中shared_pool_size的值
不能一味增加shared_pool_size,建议上限为200M。
yansongonline的理解可能有点问题: 我估计楼主的ORACLE环境绝对不可能只分配4M的shared_pool空间,
而是shared pool中内存碎片严重,无法再分配更多的内存. 应该是存在不合理的sql应用.例如没有使用绑定变量. 查询SGA的使用状态:
select * from v$SGASTAT;
如果有必要,执行: alter system flush shared_pool 另外,oracle 本身的bug 也应该引起关注.例如8.1.7.0就有个
关于shared_pool的bug,升级到8.1.7.4后就解决了.