Re: Help with ORA-04031 error

Victor Feintuch wrote:
> 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

9.2.0.5 is not the latest and greatest patchset for 9.2, and I expect this issue has been addressed in 9.2.0.6 and later patchsets. I have not found such issues with 9.2.0.6 or 9.2.0.8, but that also may depend upon your platform, which you did not report (we're running on Solaris 5.8).

David Fitzjarrell Received on Thu Oct 26 2006 - 11:14:17 CDT