Ask the Expert

How can I solve the ORA-4030 error?

I am getting ORA-04030: out of process memory when trying to allocate 123404 bytes (hash-join subh,kllcqas:kllslt) while trying to run an application. This is happening for the fourth or fifth time.

New applications were added to this database some years ago. The issue was resolved by re-starting the server. Please let me know what parameters need tuning / increase to prevent this error in future.

    Requires Free Membership to View

The ORA-4030 error always indicates that you have not allocated enough memory. Of course restarting the instance causes the problem to go away, but only for a period of time. Think about it this way…I have a glass under a leaky faucet. Eventually, the glass will fill up, drip by drip. Once full, the water overflows and goes all over the place. If I empty the glass, the water does not go all over the place. Eventually, the glass will fill again. I have not solved the root problem, which is the leaky faucet.

By restarting the instance, all you have done is to empty the glass. The root problem is to allocate more memory. The ORA-4030 error indicates the location that needs more memory. You are trying to perform a hash join. The initialization parameter that controls this memory allocation is the SORT_AREA_SIZE, HASH_AREA_SIZE, or PGA_AGGREGATE_TARGET parameter, depending on your configuration and Oracle version. Start with the last parameter. If set, increase it. If not set, look to see if the HASH_AREA_SIZE parameter is set. If so, increase it. If not, increase the SORT_AREA_SIZE parameter.

This was first published in May 2008

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: