Re: Oracle10G error!!!!

Sybrand Bakker wrote:

> On 29 Oct 2004 06:56:03 -0700, spraveen2001_at_yahoo.com (Praveen) wrote:
>
>>Hi
>>
>>If i connect from sqlplus prompt, it is working fine. but if java is
>>trying to connect, then it is saying following error...
>>
>>(java.sql.SQLException: ORA-00604: error occurred at recursive SQL
>>level 3
>>ORA-04031: unable to allocate 4096 bytes of shared memory ("shared
>>pool","select
>>/*+ rule */ bucket_cn...","Typecheck heap","kgghteInit")
>>
>>What might be the problem?
>
>
> Your shared pool is ridiculously small. Also, by setting explicitly
> the shared pool, you won't be using automatic pool management anymore.
> You need to set db_cache_size, and remove large_pool_size,
> shared_pool_size, java_pool_size and db_block_buffers.
>

Unfortunately, that advice is incorrect on a couple of levels.

For a start, if you manually set pool sizes, then automatic pool management will still happen, but the manual settings are taken as minimum sizes. Above those minima, however, dynamic and automatic re-allocation can still happen.

Secondly, the advice to go totally manually or totally automatic is not actually bad, but to remove the manual settings for large_pool, shared_pool, java_pool and db_block_buffers, only then to manually set db_cache_size is not the way to do it.

DB_cache_size is the manual mechanism to set the size (or the minimum size) of the buffer cache in 9i and above.

What you should actually set is a realistic SGA_MAX_SIZE, and SGA_TARGET. Those are the parameters which say how big your SGA as a whole should be, within which automatic allocation of pools can take place. There is an interesting interaction between the two parameters, and that was discussed in a thread here just three or four days ago.

So: I second Sybrand's advice to remove the daft minimum sizes you have currently set. You SGA_TARGET is currently about 160MB, I think. That is not hopelessly inadequate, and it very much depends on what you are doing with this database, but I would certainly bump that up to over 200MB.

HJR Received on Fri Oct 29 2004 - 18:28:16 CDT