oracle中查詢v$session報ORA-04031錯誤解決辦法

客戶的資料庫在出賬期間有工具登入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錯誤