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.
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