Problem solved: 0RA-04031, memory leak 8.1.6

From: Peter <noemail_at_noemail.com>
Date: Fri, 11 Jul 2003 14:21:41 +1000
Message-ID: <bele3r$l7h$1@bunyip.cc.uq.edu.au>

Don't you hate it when you have to run old databases, 8.1.6.3 on Solaris, because of the application that depends on it?

Anyway, we were experiencing ORA-04031 error as a result of a memory leak which caused the following:

SELECT POOL, NAME, BYTES
FROM V$SGASTAT
ORDER BY 3;

POOL        NAME                            BYTES
----------- -------------------------- ----------
shared pool library cache                 3385992
shared pool db_block_buffers              6800000
java pool   free memory                  10002432
shared pool free memory                  31385532
shared pool state objects               111558416       <------- VERY LARGE
            db_block_buffers            409600000

As can be seen, state objects is quite big when it shouldn't be.

A search on metalink with the following keywords desribed the bug and how to fix it.

diagnosing resolving error ORA-04031 bug

The workaround, since we can't upgrade yet, was to add the following hidden parameter to the init.ora file:

_db_handles_cached = 0

All seems good now. Received on Thu Jul 10 2003 - 23:21:41 CDT