Problem solve Get help with specific problems with your technologies, process and projects.

Error message when software is at its peak

On opening my software application -- built on Visual Basic and Oracle 9i -- I get an error message when the usage of the software is at its peak:

ORA-04031: unable to allocate %s bytes of shared memory

I found out this error is possibility due to the number of sessions that has been created. I don't understand why this is happening. I'm checking the codes of the software to see if there is unnecessary connections opened and to see if there are many record sets opened that may cause a session(possible at times). Please note: once one user, say who has a lot of sessions created on using the software over a period of time, closes his application the sessions get killed. Please tell me as to why these sessions get created. Is it because of improper coding, or if the Oracle tuning has to be looked into? At present, the number of processes is 1,900 and the sessions are 2,095 in the running and spfile.

It looks like your Shared Pool is not large enough. When this happens again, query V$SGASTAT and look for the entry for the 'shared pool' and 'free memory'. If the amount of free memory is low, then you will want to increase your SHARED_POOL_SIZE initialization parameter. It is highly likely that you will have to bounce the database for this parameter to take affect.

Additionally, there is a bug in early versions of Oracle 9i that caused the Shared Pool to mismanage its memory usage, resulting in this error. To fix this bug, you will have to apply the Oracle patchset.

Dig Deeper on Oracle database backup and recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.