In Oracle 8.1.7, Ora-04031 errors are often caused by fragmented shared pool
in the SGA (system global area) of the Oracle instance. There is a known bug
for this error which is introduced in Oracle 8.1.7 version and not present
in earlier releases. This bug is a memory leak problem and is addressed in
the Oracle 8.1.7.2 patchset. If your DBA has not applied the patchset, there
is a workaround. Use the following unpublished parameter in the
init.ora file for your instance:
_db_handles_cached=0
This will cause the handles created in SGA not being cached and thus avoids
the memory leaks. The side effect is a bit of performance impact, but not
much.
There are other things that you can suggest your DBA to do to avoid the
fragmentation of shared pool, such as pin the commonly used packages in the
shared_pool to avoid repeated reloading. To name a few of these packages,
they include SYS.DBMS_STANDARD, SYS.DBMS_APPLICATION_INFO, etc.
Hope this helps.
> Dong Wang
> Grand Central Networks
> [EMAIL PROTECTED]
-Original Message-
From: Ann Bruyneel [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 16, 2001 2:44 AM
To: [EMAIL PROTECTED]
Subject: ORA-00604 and 0RA-04031 errors on connect
Oracle 8.1.7.0.0
Solaris 5.8
DBI 1.18
DBD 1.07
We run a script that makes every second a connection to the oracle database,
read out a table and disconnects.
After a while (10 hours the last time) we've got these messages :
DBI-connect(TEST) failed: ORA-00604:error occured at recusive SQL level1
ORA-04031: unable to allocate 4200 bytes of shared memory
I've checked : each time we connect, we disconnect.
Is it possible that after a disconnect the DBI::DBD doesn't give all the
resources back -> so there is a kind of cumulative error
Ann
