Re: Why - ORA-04031: unable to allocate 740 bytes of shared memory

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3b669126$0$8512$ed9e5944_at_reading.news.pipex.net>...
> you could try issuing
> alter system flush shared pool;
> immediateley before running the batch job.
>
> It might also be worth contacting the vendor to see if they have a patch for
> the issue.
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
>

If you get 4031, flush probably won't work either. If Oracle can flush, it can also age out in that case, you won't get 4031 in the first place.

Basically you are out of shared pool space and no SQL is in 'recreatable' state.

The immediate pain killer is to increase shared pool, but it is a wrong solution in the long run. Your problem is probably not sharing SQL because of not using bind variables or not sharing objects. For that, you have to go to the vendor. The most common reason is not using bind variables.

The other thing you can check is cursor_space_for_time. You should set it off in you case. I bet it is probably on. If you are not sharing SQL, you should turn it off. Received on Tue Jul 31 2001 - 18:00:54 CDT