Oracle Query v$session report ORA-04031 Error Resolution

The customer's database has tools to log in to an Oracle database during transfers occasionally ORA-04031, after analysis because the tool needs to query v$session, which is identified as a bug 12808696–shared pool memory leak of " Hng:all Sessi "Memory (Doc ID 12808696.8), reproduce the error as follows
Node 1 for query report ORA-4031

code is as follows

copy code

sql> Select COUNT (*) from v$session; &NBSP Count (*) ---------- 1536   sql> Select COUNT (*) from gv$session; &NBSP 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, I Nstance OCS_DB_2:ZJOCS2 (2) ora-04031:unable to allocate 308448 bytes of shared memory ("Shared Pool", "UNK Nown object "," SGA Heap (1,0) "," Hng:all sessions data for API. ")

Node 2 for query report ORA-04031

The code is as follows

Copy Code

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. ")

Through the above analysis: Confirmation is node 2 v$session encounter bug 12808696, resulting in the node in the query v$session and Gv$session reported ORA-04031, and in Node 1 query v$session normal, query gv$ Session newspaper ORA-04031.

Bugs are fixed in 11.1.0.6, all versions of 10g are not repaired and can only be temporarily avoided by a temporary reboot, noting that the bug cannot be resolved through flash Shared_pool
If you have permission to progress a query SR 3-7670890781: Query v$session blocking_session field, a ora-04031 error occurred