Solved

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

Posted on 2003-12-09
5
1,006 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 100 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

636 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