sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle报错ORA-04031 用户在查询v$session报错

时间:2016-07-01 10:56   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 运维DBA反映,有工具登录Oracle数据库偶尔性报ORA-04031,经过分析是因为该工具需要查询v$session,经过分析确定是Bug 12808696

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

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


--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-04031 用户在查询v$session报错

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/666.html

Oracle研究中心

关键词:

ORA-04031

Oracle报错ORA-04031

用户在查询v$session报错