Re: ORA-00604, ORA-04031

On Dec 3, 10:53 am, Steve Robin <ocma..._at_gmail.com> wrote:
> ORA-00604: error occurred at recursive SQL level 1
> ORA-04031: unable to allocate 3896 bytes of shared memory ("shared
> pool","select file#, block#, ts# fr...","sga heap(1,0)","kglsim object
> batch")
>
> I got these error in oracle 10g, I was not able to investigate the
> reason, because I was not able to connect with database. I needed to
> kill process and then restart the database.
>
> I got following line in mmon trc file.
>
> SGA POLICY: Cache below reserve and cant get memory from any other
> component.
>
> sga_target = 380M
> sga_max_size = 380M
>
> Oracle : 10.2.0.2
> OS : SunOS 5.9
>
> Please let me know if I cann't find the cause for it, as well
> solution.

The reason is fairly clear: your shared pool isn't large enough. The cause of _that_ may be unknown, however.

Have you monitored v$sgastat lately? You'll find information to help you in that view, including how (generally speaking) your various pools are allocated and how much free space you have in each.

When do you see these errors? A bit more detail regarding the circumstances surrounding these occurrences would be most helpful.

David Fitzjarrell Received on Mon Dec 03 2007 - 11:07:45 CST