Help with ORA-04031 error

From: Victor Feintuch <pinpotter_at_hotmail.com>
Date: 26 Oct 2006 08:43:31 -0700
Message-ID: <1161877411.816175.40520@m73g2000cwd.googlegroups.com>

We upgraded our version of Oracle from 9.2.0.4 to 9.2.0.5.0 . Sqlplus is now reporting the following error for some users on some tables.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.5.0 - Production

SQL> select count(*) from dba_rules;
select count(*) from dba_rules

                      *

ERROR at line 1:
ORA-04031: unable to allocate 4194304 bytes of shared memory ("large pool","unknown object","hash-join subh","QERHJ Bit vector")

We increased the large_pool_size and the shared_pool_size. We are getting this error message for a normal user if we do count(*) on an empty table.
If we are doing the same command as sysdba we can see that is has 0 rows.

Otherwise get the standard solution "increase your large_pool_size".

That did not seem to work.

For another table it helped to export,drop and import that table.

The reason why we upgraded was that if we tried to login with a connectstring it failed to allocate 4(!) bytes. The upgrade did solve this problem.

We also found out that the problem occurs when a session is created while logging in via a connectstring instead of exporting the ORACLE_SID environment variable and just using slplus."

Any ideas?
TGIA,
Victor Received on Thu Oct 26 2006 - 10:43:31 CDT