Re: Resize shared_pool / Avoid Ora-4030

Hello,
 I would think, that ORA-4030 has nothing to do with shared pool: $ oerr ora 4030
04030, 00000, "out of process memory when trying to allocate %s bytes (%s,%s)"
// *Cause: Operating system process private memory has been exhausted // *Action:

 you are running out of process memory, check the OS settings for max data segment size, and ulimit settings.

 fiddling with shared pool is not going to help.  if you were talking about ora-4031, that would be different though.

 I never had the paitience to wait to the 'reduce shared pool size' call to return. they are waiting until a contigous chunk of the shared pool becomes free, which may take a while on a busy system (i.e. i let it run for a week and it did not finish).

HTH Karsten

Stephan Bressler <sbresslerx_no_spam_at_arcor.de> wrote in message news:<bu5o32$rbt$1_at_news.mch.sbs.de>...
> Hi NG,
>
> I'm thinking about avoiding ora-4030 errors due to many, many unique big
> statements using the memory resizing feature of oracle 9i. Flushing the
> shared pool usually is of no help.
>
> Therefor I suggest the following procedure:
> 1. configure shared_pool_size initially to e.g. 300M
> 2. each night (or any other low-load-period),
> resize shared_pool_size to 100M
> 3. immediately after that, resize to 300M
>
> This would give a 200M totally free, unfragmented shared_pool.
>
> In theory! In praxis the "alter system set shared_pool_size=100M" lasts
> for 1 hour and is still running. CKPT eats up 1 CPU.
> I'm running 9.2.0.3 on Linux.
>
> Any ideas / suggestions?
>
> Stephan
Received on Thu Jan 15 2004 - 10:09:22 CST