Recently we encountered ora-4031 at frequent intervals. We're unable to find the cause of this problem. Workaround is shutdown/startup db. However, this is unacceptable as this is a 24x7 operations.
Our original share pool was 100MB, and increased to 500MB, but the problem persists.
We pinned all packages, functions, triggers, triggers at start up, and problem still occurred.
Our Oracle version is 18.104.22.168.1 and os is Compaq Tru64 v4.2 unix.
How do we monitor and trace this problem? And most importantly, fix it.
will you please recheck the error number.
Are you sure it is ora-4031 ?
Yes, it is ora-4031. You can do an "oerr ora 4031" at the unix prompt to get the full description.
Is it ora-04031 ?
i feel you need to reconfigure your Unix kernel interprocess communication parameters to accomodate the SGA structure of the Oracle server.
obtain a list of the system's current shared memory and semaphore segments and try and reset the SHMMAX & SHMSEG parameters according to your requirement (keeping in mind the number of separate instances you are going to have on your server)
The total allowable shared memory is determined by the formula SHMMAX * SHMSEG.
Do make sure to rebuild the kernel if you have changed the semaphore parameters.
This might be a possible solution to the above problem. i had once faced such a problem and i had solved it in the same way .
I might be wrong in my guess though.
I don't think this is related to kernal parameters.
An ORA-4031 error is related to poor use of one of the memory pools. The error message will usually specify which pool it is trying to use when you get the ORA-4031. I would not be suprised to find you have shared pool fragmentation in your shared_pool.
See http://metalink.oracle.com/metalink/...&p_id=146599.1 for details on diagnosing and resolving this error.
Thank you so much Suvashish and Jeff for your replies and assistance.
We managed to isolate the problem. It was an intraweb application that we had running which somehow kept grabbing memory. It has since been taken off and things are back to normal.
I shall study the metalink document closely nevertheless as there's definitely much more we could do to configure the sga and share pool properly. Strangley, Oracle support failed to point us to this document.
Thank you again, one and all!
Click Here to Expand Forum to Full Width