Solved

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

Posted on 2003-12-09
5
997 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query in Oracle forms Builder 2 42
Extract the first word (before the , ) 2 40
Wrong number of values in the INTO list of a FETCH statement 16 47
SQL Query 34 82
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

861 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now