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

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
soma_srinivasAsked:
Who is Participating?
 
baonguyen1Connect With a Mentor Commented:
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
 
baonguyen1Commented:
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
 
soma_srinivasAuthor Commented:
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
 
baonguyen1Commented:
You are welcome. Hope it works for you

Baonguyen1
0
 
soma_srinivasAuthor Commented:

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.