I ran your script a dozen times or so (9.2.0.5 64 bit RAC on AIX 5.2) and sure enough 04031 errors starting poping up. Unfortunately I do not have any more time to look at this, but perhaps one of the board guru's will look. I was wondering if you have brought this issue to Oracle support attention? -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Daniel Wittry Sent: Tuesday, December 21, 2004 2:43 PM To: oracle-l@xxxxxxxxxxxxx Subject: Statspack (shared pool) memory leak Has anyone else been collecting statspack snapshots on a regular interval and been experiencing shared pool memory leaks? Querying segment statistics (v$segment_statistics, v$segstat, or x$ksolsfts) leaks about 1400 bytes per query. After a short while, the dreaded ORA-4031 fragmented shared pool error occurs. V9.2.0.1 thru 10.1.0.3 all leak (tested on Sun, Linux, Win, and OraMVS). The script below is one of many attempts that have resulted in a fragmented shared pool. I have tried with and without a large pool, execute_immediate, using rownum, and not using rownum, a pl/sql loop and not using pl/sql, just plain sql with a series of slashes /, /, /, /, (to repeat the last sql command). Everything leaks. CAUTION: do not run this on an instance unless you can bounce it when done. set heading off column what format a40 column value format a30 spool expose_leak_2.log select 'db instance' what, user || '@' || global_name value from global_name UNION select '# rows in v$segstat', to_char(count(*)) from v$segstat ; set linesize 200 set time on set serveroutput on size 300000 declare l_temp char(1); l_before number; l_after number :=3D 0; l_loop_times pls_integer :=3D 100; -- try 1000 l_sleep number :=3D 0.00; -- makes no difference =20 cursor c_seg is select * from v$segstat; r_seg c_seg%ROWTYPE; function get_mem return number is cursor c_mem is select bytes from v$sgastat where name =3D 'free memory' and pool =3D 'shared pool'; r_mem c_mem%ROWTYPE; begin open c_mem; fetch c_mem into r_mem; close c_mem; return r_mem.bytes; end get_mem; begin l_after :=3D get_mem(); -- initialize for x in 1..l_loop_times loop l_before :=3D l_after; --execute immediate ('select ''x'' from v$segstat s1 where rownum = =3D 1') -- into l_temp; OPEN c_seg; FETCH c_seg INTO r_seg; CLOSE c_seg; -- l_after :=3D get_mem(); dbms_output.put_line ('Loop ' || x || ': (' || to_char(sysdate,'hh24:mi:ss') || ') from ' || to_char(l_before,'999,999,999') || ' to ' || to_char(l_after,'999,999,999') || ' (loss of ' || to_char((l_before-l_after),'9,999,999') || ')'); dbms_lock.sleep(l_sleep); end loop; end; / spool off -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l