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

What operating system? IF *NIX have you verified kernel parameters are set correctly?

As fitzjarrell says ... why 9.2.0.5? If you are going to patch, and you should, go to the latest patchset.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org