oracle中查询v$session报ORA-04031错误解决办法

时间：2022-06-29 09:55:45 编辑：袖梨来源：一聚教程网

客户的数据库在出账期间有工具登录Oracle数据库偶尔性报ORA-04031,经过分析是因为该工具需要查询v$session,经过分析确定是Bug 12808696 – Shared pool memory leak of “hng: All sessi” memory (Doc ID 12808696.8),重现错误如下
节点1进行查询报ORA-4031

代码如下

复制代码

SQL> select count(*) from v$session;

COUNT(*)
----------
1536

SQL> select count(*) from gv$session;

COUNT(*)
----------
2089

SQL> select /*+ full(t) */ count(*) from gv$session t;

COUNT(*)
----------
2053

SQL> select * from gv$session;
select * from gv$session
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server PZ93, instance
ocs_db_2:zjocs2 (2)
ORA-04031: unable to allocate 308448 bytes of shared memory ("shared
pool","unknown object","sga heap(1,0)","hng: All sessions data for API.")

节点2进行查询报ORA-04031

代码如下

复制代码

SQL> select * from gv$session;
select * from gv$session
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server PZ95, instance
ocs_db_2:zjocs2 (2)
ORA-04031: unable to allocate 308448 bytes of shared memory ("shared
pool","unknown object","sga heap(6,0)","hng: All sessions data for API.")

SQL> select * from v$session;
select * from v$session
*
ERROR at line 2:
ORA-04031: unable to allocate 308448 bytes of shared memory ("shared
pool","unknown object","sga heap(7,0)","hng: All sessions data for API.")

通过上述分析:确认是节点2的v$session遭遇到Bug 12808696，导致在该节点中中查询v$session和Gv$session报ORA-04031，而在节点1中查询v$session正常，查询Gv$session报ORA-04031.

bug在11.1.0.6中修复,所有的10g版本中未修复,只能通过临时重启来暂时避免,注意该bug通过flash shared_pool无法解决
如果您有权限可以进步一查询SR 3-7670890781: 查询v$session的BLOCKING_SESSION字段时，出现ora-04031错误

推荐专题

最新下载

热门教程

oracle中查询v$session报ORA-04031错误解决办法

相关文章

热门栏目

php教程

asp.net教程

手机开发

css教程

网页制作

办公数码

jsp教程