Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

oracle error 4031 - unable to allocate %s bytes of shared memory

Posted on 2003-12-09
5
Medium Priority
?
1,008 Views
Last Modified: 2013-12-11
Hi,

I am using oracle 8.1.7 database for my JSP web application which generates various reports.
I got oracle error 4031 when I tried to access the reports through browser (which actually does a select query for the reports).

I shutdown the database and started it. Now it started working. But unfortunately the problem is recurring. I keep on getting the error once in two days or so. Also, when the error occurs,
it will not allow me to connect to oracle immediately for restarting the database. When I try for some number of times ( may be 5, 6 or 7 ), randomly it connects at some point of time.

Following is the error description I get :
ORA-04031: unable to allocate 4168 bytes of shared memory ("shared pool","unknown object","sga heap","state objects")

Can anyone suggest a permanenet solution ?

Thanks
Srinivas Soma
0
Comment
Question by:soma_srinivas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 8

Expert Comment

by:baonguyen1
ID: 9909406
You have some ways to work around:

1. Set this parameter in your init<SID>.ora file and restart the database:

_db_handles_cached = 0

If it works, seem that you have to apply patch 8.1.7.2 or higher. It is a bug of  8.1.7. After applying the patch you should remove this parameter

2. Consider to increase the SHARED_POOL_SIZE. Calculate the Cache Hit Ratio to measure the usage usage of the shared pool based on how many times a SQL/PLSQL statement needed to be parsed instead of being reused:

SQL>SELECT SUM(PINS) "EXECUTIONS",
            SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
            FROM V$LIBRARYCACHE;

If the ratio of misses to executions is more than 1%, try to reduce the library cache misses by increasing the shared pool size.




0
 

Author Comment

by:soma_srinivas
ID: 9920643
Hi Baonguyen1,

I checked the ratio of misses to execution, Its actually very far less than 1 %.

Regarding setting the parameter and applying patch,  I need to wait for some time because its shared by many other people.

Anyway thanks a lot for your help..

Thanks
Srinivas Soma
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 9921095
You are welcome. Hope it works for you

Baonguyen1
0
 

Author Comment

by:soma_srinivas
ID: 10005564

Hi Baonguyen1,

I set the parameter as suggested by you and applied higher version patch.
Now, my oracle is not crashing now and then as it used to happen earlier.
That solves my problem.

Thanks a lot for your help

Regards
Srinivas Soma


0
 
LVL 8

Accepted Solution

by:
baonguyen1 earned 400 total points
ID: 10009071
You are welcome. If it works can you accept the answer to remove it from the waiting list. Thanks

Happy New Year to you.

Cheers,
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question