I have a java application running on OC4J904 and connecting to Oracle 8.1.7 using JDBC. My DB die when i implemented the application and found out that the shared pool memory has been used out. So I increased the shared pool size to 500MB but after a few days the free memory still become very low. My application is quite small so Im not sure why it taken up so many resources.
Is there any way to avoid Oracle from using up the shared pool or configure the Oracle to free out the memory, or is better to write script to automatically flush the shared pool when its low?
I think you have a memory leak (bug). Check Oracle for a patch.
I do not think the problem is your shared pool being too small. The shared pool has a built-in aging mechanism, and Oracle should not run out of memory unless everything is needed at once, which does not sound true in your case.