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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
baonguyen1Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.