Pro*c app causing ORA-04031

Hi,

We have a pro*c application that reads records from a text file and inserts them into a database table, committing every few thousand records. The app doesn't use bind variables, the result being that we get an increasing number of SQL insert statements in the shared SQL area (one for each record inserted). After a couple of hours the app crashes with an ORA-04031 error, i.e. out of memory in the shared pool. Why are the insert statements never removed from the shared pool - I would have thought they would at least be aged out once memory started running low?

I realise that the app should use bind variables, and it is currently being re-written to do just that, but I am curious as to why the resources are never freed up. I would be grateful if anyone could shed any light on this. Received on Sat Oct 14 2000 - 09:58:06 CDT